Previously I showed you how to get single related model per each parent, while eager loading the hasMany relation. That was pretty easy and we just made the most of the Eloquent.

However, most likely the time will come, when you need several related models per parent.

It won’t be as easy, but it’s far from impossible, so let’s do it!

Imagine classic example:

Post hasMany Comments

First, let me show you how to achieve this in MySQL

We need to group the results by foreign key. For this let’s use MySQL variables:


Note: you can’t use group as an alias, for it’s reserved word of course.

Next add simple rank column:


Then adjust @rank so it applies to each group:


This way we can work with rank and use it to limit the result to N per group, which is exactly what we need.

You have surely noticed that each query is preceeded by a SET statement. That’s basic method to set a variable in MySQL, however it’s rather tedious, and it will require another DB::statement call in our code. Instead I will use handy inline variables setting:

SELECT @group := 0, @rank := 0

So, let’s look at the final query that we will execute to fetch 2 latest comments per post:


Finally translate it to Eloquent, for example as a BaseModel scope:

Here’s all the code in single method, nothing extracted for presentation purpose


And extending it Post model:


Now we can fetch 5 comments per post with eager loading as easily as this:




Now you have a handy method for getting N per parent for hasMany relation like above.

Share your thoughts about it in the comments.

It's only fair to share...Tweet about this on TwitterShare on RedditPin on PinterestShare on FacebookShare on Google+

Related Post

Laravel – querying any level far relations w... Eloquent provides one Relation type for far related tables - hasManyThrough. However it works only with with cascade of hasOne/hasMany relations a...
Tweaking Eloquent relations – how to get lat... Have you ever needed to show only single related model from the hasMany relationship on a set of parents? Being it latest, highest or just rand...
Querying relations with Eloquent in Laravel 4 Laravel ORM Eloquent is brilliant! That being said, let's try to tackle very common questions that appear when you try to get related models lo...
Querying relations in Laravel: get Models where la... Another part of Querying relations in Laravel will cover such problem: I want to get SomeModels that have latest RelatedModel (hasMany) matchin...