Converting part of query to subquery (for paginating queries with joins)

Questions / Help
,
1

Hello fellow Alchemists!

I have a problem like this:

I have User schema which has_many address Address. I want to paginate users with their addresses and get first page with 10 users like this:

query =
  from u in User,
  where: ilike(u.name, "A%"),
  join: a in assoc(u, addresses),
  order_by: u.id
Repo.paginate(query, 1, 10)

Scrivener operates on database rows, so if each user has 3 addresses, I will get 3 users with 3 addresses + 4th user with one address because underlying join expression returns:

u1, a1
u1, a2
u1, a3
u2, a4
u2, a5
u2, a6
u3, a7
u3, a8
u3, a9
u4, a10

I am thinking about writing my own custom pagination function that would change the from expression to subquery, so from

query =
  from u in User,
  where: ilike(u.name, "A%"),
  join: a in assoc(u, addresses),
  order_by: u.id
my_custom_paginate(query, 1, 10)

I would like to get:

from subquery(from u in User, 
  where: ilike(u.name, "A%"), 
  limit: 10),
  join: a in assoc(u, addresses),
  order_by: u.id

Is it possible to modify the query like that with existing Ecto.APIs or do I need to dig into internals?
Do you have some other solution to solve such problems or do you usually first load users and then preload other associations with subsequent queries?

2 Likes
2

The more I digged, the more I understood that Ecto doesn’t provide such functionality and that is probably for the better. Instead of modifying the original query, it is better to be explicit.

One idea that @wojtekmach showed me is to add preloads in the paginate funciton like this: Repo.paginate(query, pager, per_page, preloads: [:addresses]) A function that provides both pagination and preloads is explicit. Plus quite often, two queries to database would be more efficient than one because of the data duplication I showed in previous post (where each user was duplicated three times).

3 Likes