Hello! I need to update a dynamically built query such that items with photos order before items without photos. A Photo
belongs to an Item
.
What I would do in SQL is left join on the association table (“photos”), select a named count via COUNT(photos.id) AS photos_count
, and then either order by it directly, or order by a case statement where photos_count > 0
(or IS NOT NULL …).
I am having a very difficult time expressing this with Ecto. I need to define this in a small helper function that is piped into an existing list of query building functions, so I dont have much control over how tables are aliased. Furthermore, all of my efforts to define a named COUNT result in an error like:
undefined function escape_count/0
Ive tried via: query |> select_merge([item, ..., photo], %{photos_count: count(photo.id)}
Or: `query |> select_merge([item, …, photo], fragment(“COUNT(?) AS photos_count”, photo.id))
Or (guessing here) select_merge([item, ..., photo], %{has_photos: fragment("1 CASE (?) IS NULL THEN 0 ELSE 1", photo.id)})
I couldnt find a way of doing this directly in the order_by
clause, though that feels unintuitive anyway.
I would just do this in raw SQL, but being part of a dynamically built query, I cant control the table names and such. I also cant do an in-memory sort with elixir code, since other parts of the query control pagination, etc.
How can I append to an existing query, some clauses that will allow me to order by the presence of associations?