Recursive Eager Loading + Limit and Sorting Support

Based on what I can see at the moment (i could be wrong - i can't get the demo to fully work for me so i can't be sure), eager loading will only work for the current resource + its immediate includes. Going any more than one level down with includes will still lead to a diluted version of the original N+1 problem.

This issue isn't that urgent - it affects deeply nested includes only and perf issues would likely only be seen on reasonably sized data sets.

This is mostly a brain dump for myself (happy to have thoughts though) to allow for some time to think on this and possible approaches.

For example, for the following include string assuming it's in the context of a top level post model: id,user{email,id},comments{user{email,id,comments{post{id}}

This breaks out into something like:

posts:post.user, post.comments - Query Count: 3
for each post : comments.user - Query Count: Number of posts
for each post comments comments.user, comments.post - Query Count: Number of posts + Number of posts * Number of Comments

Dealing with eager loading recursively

Posts -> run immediate include to get user items (don't transform the users)
Collect all comment models from comment collections -> run eager load of user relationship across the entire collection of models
Run transform on comments
... something

Dealing with limits, ordering and eager loading

Limits on sub relationships aren't supported in eloquent, something like this might work

An example of how to resolve relevant post ids for each user given a statement like posts:(limit:2)

SELECT user_id, SUBSTRING_INDEX(GROUP_CONCAT(id),",",2) as post_ids
FROM posts 
WHERE user_id IN(?,?,?) #merge user ids in
GROUP BY user_id
ORDER BY user_id, created_at

GROUP_CONCAT has some limitations if you're working with large related limits.
postgres has equiv SUBSTRING_INDEX(GROUP_CONCAT(id),",",2) = array_agg(id)[1:2]

rexlabsio / smokescreen-laravel-php

Recursive Eager Loading + Limit and Sorting Support #1

Dealing with eager loading recursively

Dealing with limits, ordering and eager loading