Hi I am trying to preload associations for a subquery like such:
from(p in Post, distinct: [p.group_id], order_by: [asc: p.published_date], preload: [:comments]) |> subquery() |> limit(some_limit) |> order_by([q], asc: q.title)
but Ecto is throwing a
** (Ecto.QueryError) cannot preload associations in subquery in query.
Is there another way to get around this error? My use case specifically require the use of subquery because I am using Postgres DISTINCT ON clause and Postgres dictates that the ORDER BY must then follow the columns specified in the DISTINCT ON clause.
To shed more light on the use case, imagine I have a table (Post) with a column called group_id (and many Posts will share the same group_id), and my query will returns only 1 Post per group_id where the Post with the earliest published date among the group will be returned. This is easily achievable by
distinct: [p.group_id], order_by: [asc: p.published_date] but for the full result sets I actually want them sorted by Post’s title, hence the use of subquery but using subquery somehow eliminate the possibility of preloads. I know there are other ways (that are more SQL-oriented as DISTINCT ON is a Postgres feature like window function) but I find DISTINCT ON the simplest to reason with.
Hence my question is, is there a way to still use preload for subquery?