Preloaded associations with joins

[mbernerslee]

If I preload accociations using a query that joins the tables, the number of rows returned by the query may be more than the number of rows that are displayed. Kerosene correctly works out the number of rows to display, but then uses this to limit the rows in the query.

For example:

from p in Pet,
join: vb in VetBooking, on: p.id == vb.pet_id
preload: [vet_booking: vb],
select: {p, vb}

If you had 2 pets each with 2 bookings, the query would return 4 rows but only 2 rows should be displayed (one for each pet). Kerosene correctly works out there are 2 rows, but say per_page = 2 it would add limit 2 to the query, which could just return 1 pet on Page 1... since those 2 rows could be the bookings for 1 pet.

This can cause a number of issues:

• some expected results do not appear on any pages at all
• Some pages will show less than the max allowed per_page results, even though there are subsiquent pages

This looks related to https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-queries

Note: keep in mind operations like limit and offset in the preload query will affect the whole result set and not each association. For example, the query below:

comments_query = from c in Comment, order_by: c.popularity, limit: 5
Repo.all from p in Post, preload: [comments: ^comments_query]

won’t bring the top of comments per post. Rather, it will only bring the 5 top comments across all posts.