Subquery in select (group by not available)

Questions / Help
1

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. :frowning:

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.

Thanks!

2

Join the subquery using a lateral join and use parent_as/1 to refer to the parent query in the subquery.

1 Like
3

Thanks @LostKobrakai

I’m struggling with the Ecto syntax of what you mentioned. :frowning: I’m aware I should have enough tips to continue my searches, but complex Ecto queries are still black magic to me sometimes.

Here’s my take:

      def sort_by_popularity(query \\ __MODULE__) do
        applications_query = from(a in UserApplication, select: count(a.id), where: a.certification_id == parent_as(:certification).id)


        from(q in query, 
          inner_lateral_join: c in subquery(applications_query), as: :count,
          order_by([desc: :count])
        )
    end

I get:

 ** (ArgumentError) invalid binding passed to Ecto.Query.join/5, should be list of variables, got: c in subquery(applications_query)
     lib/ecto/query.ex:1071: Ecto.Query."MACRO-join"/6
     expanding macro: Ecto.Query.join/4

Besides, any chance to use assoc(q, :applications) instead of parent_as(:certifications) to make it table agnostic?

Thanks a lot