Ecto: query view with same structure as model

Questions / Help
1

Hello,

So I have this materialized view that contains the same fields as my table with extra conditions:

      CREATE MATERIALIZED VIEW searchable_certifications AS (
        SELECT * FROM certifications
        WHERE certifications.is_active
        AND EXISTS (
          SELECT null FROM delegates
          INNER JOIN certifications_delegates
          ON delegates.id = certifications_delegates.delegate_id
          WHERE delegates.is_active
          AND certifications_delegates.certification_id = ?
        )
      )

I would like to query this materialized view easily.

What would be a best practice?

If I go for from(c in "searchable_certifications") I get the error:

** (Ecto.QueryError) expected a from expression with a schema in query:

So either I should create a SearchableCertification model but then how do I share the schema with the Certification model?
Or I should tell the query that the schema is from Certification. Can I?

Thanks

2

If you want to select all fields like that it must come from a schema. With just the table name Ecto doesn’t know which fields to ask for (and it doesn’t do select *). So your two choices are to make a schema or to list the fields in a select clause.

3

The schema is tied to a table so you can’t say use this non-materialized view schema to give me my materialized view. It has to be a new schema.

I stand corrected

4

That’s not correct. from x in {"other_table", Schema} does use the schema with a custom table. This tuple format can be used wherever a Ecto.Queryable is allowed.

5 Likes
5

Oh that’s really cool. Ty!

6

Amazing! Thanks @LostKobrakai !