I’m trying to replicate the following PostgreSQL query with ecto:

SELECT *
FROM (SELECT * FROM event LIMIT 1000) as e
LEFT JOIN event_runner as er ON e.id = er.event_id

The idea is to limit only the FROM part of the query, returning the correct amount of events stopping the results from the JOINs interfering with the limit count, as it would occur if I used a LIMIT clause in the main query, as there is multiple event_runners for one event, defined as a has_many in the Event schema.

That said I trying to use the current ecto query:

event_query = from e in Event, limit: ^max_rows

from f in subquery(event_query),
  left_join: er in assoc(f, :event_runners),
  preload: [event_runners: er]

However when I run the above query I get the following error:

** (Ecto.QueryError) can only preload sources with a schema (fragments, binary and subqueries are not supported) in query:

     from e0 in subquery(from f0 in MyApp.Event,
       limit: 100,
       select: f0),
       join: e1 in MyApp.EventRunner,
       on: e1.event_id == e0.id,
       select: e0,
       preload: [event_runners: er]

The error is clear, I can’t do a preload with subquery as a source schema, but the use of preload from the join part would be ideal, as we are receiving a lot of request and for each entry returned a separated query is executed if we use the Repo.preload function.

Is there any way to get around this limitation? Or, is it possible to rewrite this piece of code to make the limit only affect the FROM clause?