So I have this query over a materialized view in postgres:
SELECT s0."id", s0."rncp_id" FROM "searchable_certifications" AS s0 LEFT OUTER JOIN "applications" AS a2 ON a2."certification_id" = s0."id" GROUP BY s0."id" ORDER BY count(a2."id") DESC LIMIT 3
Which doesn’t work when using
searchable_certifications as it is a materialized view, without primary key in postgres (more details).
So I have changed my querying strategy with the following query that also works:
SELECT s0."id", s0."rncp_id", (select count(a.id) from applications a where a.certification_id = s0.id) as count FROM "searchable_certifications" AS s0 ORDER BY count DESC LIMIT 3
The problem is that I really don’t know how to write this in ecto.
Also, my previous function was table agnostic:
def sort_by_popularity(query \\ __MODULE__) do query |> join(:left, [q], u in assoc(q, :applications)) |> group_by([q, u], q.id) |> order_by([q, u], [desc: count(u.id)]) end
So ideally I keep this level of abstraction.