Ecto preloading associations for my subquery

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?

1 Like

Let’s not get fixated on how you figure this must be solved for now.

Can you give an example of what must the result set look like exactly?

1 Like

Hi @steven7 I’ve come across the exact same challenge and wondering what solution or workaround you ended up finding?

Did you try putting the preload on the outer query instead of the sub query?

I will try that, thanks