I want to build a query with dynamic order by handling. The basic sorting example id + name (see below) works fine. The sort variable is inserted in query.
favorites_count_query = from(f in Favorite,
select: %{wallpaper_id: f.wallpaper_id, count: count(f.user_id)},
group_by: f.wallpaper_id)
...
sort = [asc: :id, asc: :name] # comes from request
from(w in Wallpaper,
left_join: f in subquery(favorites_count_query), on: f.wallpaper_id == w.id,
left_join: d in subquery(downloads_count_query), on: d.wallpaper_id == w.id,
select: w,
select_merge: %{favorites_count: coalesce(f.count, 0)},
select_merge: %{downloads_count: coalesce(d.count, 0)},
order_by: ^sort,
group_by: w.id)
In addition I want to sort on count fields (favorites_count & downloads_count). Put this part static in query will work.
...
order_by: coalesce(d.count, 0),
My question now is how to handle this like the first example with dynamic variable. I didn't find a way to handle this.
sort = [asc: :favorites_count, asc: :name] # comes from request
...
order_by: ^sort,
Ends up with Unknown column 'w0.favorites_count' in 'order clause'
order_by: ^foo(sort),
def foo(sort), do: coalesce(:count, 0)
Ends up with undefined function coalesce/2 (fragment/3)
Using dynamic/2 ends up with expected a field as an atom, a list or keyword list in 'order_by', got: 'dynamic(...)'
I understand the main problem, but didn't get a solution. Any ideas or in best case solutions? Thanks for help!
The solution is call order_by/3 as function multiple times instead of generate list and put them directly in from/2.
sort = [asc: :favorites_count, asc: :name] # comes from request
from(w in Wallpaper, ...)
|> order_wallpapers(sort)
...
defp order_wallpapers(query, []), do: query
defp order_wallpapers(query, [{order, :favorites_count} = sort|others]) do
query
|> order_by([w, f, d], [{^order, f.count}])
|> order_wallpapers(others)
end
defp order_wallpapers(query, [sort|others]) do
query
|> order_by([w, f, d], ^[sort])
|> order_wallpapers(others)
end