One to many relationship in view

[NotSugat]

@c.View(`
	SELECT
		gen_random_uuid() as id,
		NOW() AS created_at,
		guideline_id,
		r.module_id AS module_id,
		r.id AS user_module_id,
		AVG(a.score) AS avg_score,
		CASE
        WHEN AVG(score) >= 4.67 THEN 'Skvělé'
        WHEN AVG(score) >= 3.34 THEN 'Dobré'
        WHEN AVG(score) >= 2.34 THEN 'Potřeba zlepšení'
        ELSE 'Vyžaduje pozornost'
    END AS performance_level
	FROM user_module AS r
	JOIN user_module_answer as ra ON ra.user_module_id = r.id
	JOIN question q ON ra.question_id = q.id
	JOIN guideline g ON g.id = q.guideline_id
	JOIN answer a ON ra.answer_id = a.id
	GROUP BY guideline_id, r.id;
`)
export class UserGuidelinePerformance {
  createdAt = c.dateTimeColumn().notNull();
  guideline = c.oneHasOne(Guideline, "userGuidelinePerformance");
  module = c.oneHasOne(Module, "userGuidelinePerformance");
  userModule = c
    .manyHasOne(UserModule, "userGuidelinePerformance")
    .setNullOnDelete();
  avgScore = c.doubleColumn().notNull();
  performanceLevel = c.stringColumn();
}

When I try to define the view table as one has many relationship. It has this issue. Is there a way to fix this? Or we can not use one has many relation with view ?

✔ Do you want to continue? › Execute migrations
  Executing...
  [
    {
      code: 'MIGRATION_FAILED',
      migration: '2025-03-21-201341',
      message: '2025-03-21-201341: Database query error: "user_guideline_performance" is not a table or materialized view\n' +
        'SQL: CREATE INDEX ON "user_guideline_performance" ("user_module_id");\n' +
        '\n' +
        'parameters: '
    }
  ]
exit status 2
exit status 2

[jonasnobile]

No, you cannot create a hasMany relation on a virtualized view because virtual views do not support indexes on foreign keys. In Contember, this results in an error when trying to create such a relation. However, the new materialized option introduced in the latest version of Contember allows you to define hasMany relations on views. Materialized views are stored as actual tables, so they can support indexes, but you'll need to refresh them manually. For more details, please refer to PR #748.

[NotSugat]

Thank you! Since it has implemented virtual view, I might have to change the approach or use materialized view based on my requirement. Thanks!

[matej21]

Hi, what changes have you made to the UserGuidelinePerformance view? Could you please share the contents of the generated migration file?

[NotSugat]

When I tried changing the relation between userModule as one has many it had the issue

 userModule = c
    .manyHasOne(UserModule, "userGuidelinePerformance")
    .setNullOnDelete();

@jonasnobile answer help solve the issue!

[jonasnobile]

Actually, you don't have hasMany relation in your view. So, there could be a bug in how we do migrations with views.

[NotSugat]

Is the version v2.1.0-alpha.6 stable to use for the production application ?

[jonasnobile]

Alpha versions are not recommended for production use. There can be bugs and unexpected behaviors.