Correctly paginate nested joins

[drewolson]

Pagination should work correctly for queries with deeply nested joins.

[supernullset]

I would be interested in helping with this! This same issue bit me about a month ago, so its relevant to my interests. Any thoughts on how I should/would start here?

[drewolson]

@supernullset thanks! I pushed two new tests to this branch. There's a failing test that we want to make pass, but also a passing test that orders on a join table. That second test needs to keep passing when we do the nested joins work.

Hope this helps!

[supernullset]

@drewolson thanks for the tip! Ill take a look at it over the weekend

[supernullset]

@drewolson It took me a few extra days to look at this, but I have been poking at the failing test, as well as reading what seems to be its root. I am a little confused by the intent behind the failing test.

To recap, we have a setup which constructs 3 Posts 3 Authors and 9 Comments. Then a query is created (I omit the call to paginate)

page = Post
|> join(:left, [p], c in assoc(p, :comments))
|> join(:left, [p, c], a in assoc(c, :author))
|> preload([p, c, a], [comments: [:author]])
|> order_by([p], desc: p.body)

If we execute this query with a Repo.all we get back 9 results; Looking at the generated SQL from the query:

SELECT p0.id, p0.title, p0.body, p0.published, p0.inserted_at, p0.updated_at 
FROM posts AS p0 
LEFT OUTER JOIN comments AS c1 ON c1.post_id = p0.id 
LEFT OUTER JOIN authors AS a2 ON a2.id = c1.author_id 
ORDER BY p0.body DESC

This makes total sense, as we should be getting a post row for every comment.

So, my question becomes: What did PR 30 solve? AFAICT, pagination is working exactly as described as I would expect it to behave. As is pointed out in Scrivener issue 29, the described situation can now be averted by using subqueries (since ecto 2) is now available; I just want to make sure that I understand the supposed bug that is represented by this test before I go off trying to fix something :).

[drewolson]

@supernullset Thanks for such a thorough investigation. I think the general idea of the PR was that, if you query Post, you should never get back more entries than there are in the table.

That said, I'm on your side on this one. I'd prefer to not fix this "problem" but rather rely on sub-queries in Ecto 2.0 to help. Regardless of what "solution" was devised, we'd be making subjective decisions. I'm 👍 for closing the issue at this point.

Thanks again, let me know what you think.

[supernullset]

@drewolson sure thing!

I'm with you on not changing ecto's behavior. Yay for closing issues!

[csuriano23]

Is this issue still not addressed? I am experiencing a wrong behaviour on result size when paginating a joined ecto model.

In particular when executing a one-to-many join like:

SELECT
	b0.`field1`,
	b1.`field2`
FROM
	`parent` AS b0
LEFT OUTER JOIN `child` AS b1 ON
	b1.`parent_id` = b0.`id`

If ask for page 2 with size 3, the query is resolved as:

SELECT
	b0.`field1`,
	b1.`field2`
FROM
	`parent` AS b0
LEFT OUTER JOIN `child` AS b1 ON
	b1.`parent_id` = b0.`id`
LIMIT 3 OFFSET 3

and if the rowset contains three rows pointing to different children with the same parent id, I will have a result with length 1, even if this isn't the last page.

Is this known? Are there workarounds?

EDIT: I've found a workaround executing my queries twice; the first time I execute the base query with joins needed for filtering and I extract the unique IDs of the entities, then I apply Scrivener pagination and I obtain a single page with just IDs.
At last I execute the full query with 1:N joins, but adding an extra where-condition on the IDs that I need for the current page.

This implies that I can't have a filter on a field of the table that is in the right side of a 1:N relationship