Laravel Video Tutorial: Eloquent Eager Limit

In this tutorial I invite you to discover the eloquent extension eloquent-eager-limit which allows you to use the SQL windowing functions on Eloquent.

The problem

For a recent project I was asked to retrieve the last 10 posts with the latest associated comments for each of them. The first solution is to retrieve the comments in the element traversal loop

$post->comments()->limit(3)->latest()->get();

This generates the n+1 problem and generates 11 queries in our case. We could mitigate the problem with the cache but that then implies setting up a cache invalidation policy which can be complex.

Another solution is to retrieve all the comments for the articles and then list only the last 3 of the collection.

$posts = Post::limit(10)->with('comments')->latest()->get();

This approach uses a lot of memory by fetching comments that won’t be used.

The solution

For this kind of problem it is possible to recover the comments using the PARTITION system.

SELECT * FROM(
    SELECT 
        post_id,
        ROW_NUMBER() OVER (PARTITION BY post_id) as row_number,
        *
    FROM comments
    WHERE post_id IN (7, 6, 5, 4, 3)
) as t
WHERE t.row_number <= 5;

The eloquent-eager-limit library will precisely allow you to generate this kind of request directly from Eloquent. Once the traits have been added to our models, we can preload the relationships by adding a limit.

$posts = Post::limit(10)
    ->with([
        'comments' => function ($query) {
            return $query->latest()->limit(3);
        }
    ])
    ->latest()
    ->get();

Now when we retrieve comments from an article, we will have 3 elements.

$post->comments // 3 commentaires