Unusual Select Query

Questions / Help
1

Does anyone know how I would write this in Ecto?

results.tags_array is an :array type. In Postgres, this query returns a list of distinct tags seen in last 100 results for a given user (“recent tags”).

select array_agg(DISTINCT c)
from (
  select unnest(tags_array)
  from results
  where user_id = 28
  order by done_at desc
  limit 100
) as dt(c)
2

You can try the following in Ecto master:

query =
  from r in "results",
    where: [user_id: 28],
    order_by: [desc: :done_at],
    limit: 100,
    select: %{entry: fragment("unnest(?)", r.tags_array)}

from q in subquery(query),
  select: fragment("array_agg(DISTINCT ?)", q.entry)

If that doesn’t work, I would recommend writing the query directly with Ecto.Adapters.SQL.query!(repo, query, params). If you don’t need composition and just need to run the query as is, using Ecto.Adapters.SQL.query!/3 is likely the most straight-forward approach.

4 Likes