Laravel Eloquent limit results for relationship

Question

I have a simple set-up of Albums and Images, each album has many images. I can get all the data fine but I want to limit the returned number of images to 3. I have tried passing a closure like so:

Album::with(['images' =>  function($query) { $query->take(3);}])->get();

This does limit the number of images to 3 but it limits the total count of images to 3 but I want to limit each album to 3 images. So the first album will show 3 images as expected but all the other albums have no images.

I have tried adding a new method to my model like so:

public function limitImages()
{
    return $this->hasMany('App\Image')->limit(3);
}

And I call this in my controller:

Album::with('limitImages')->get();

But this doesn't limit the image count returned at all

Trubow

Have you tried using $query->limit(3) in your first Closure attempt, instead of take(3)? – Santos

Yeah I've tried swapping limit() and take() in both the controller and model but it still works the same. Returns 2 images for the first album and no images for the rest of the albums – Trubow

Same happens. This is an interesting question!! – Nur

What version of Laravel are you using? – Santos

This is 5.4 a fresh copy from yesterday (just playing around with it not a serious project) – Trubow

Alright this isn't easy. Here's a post about it. softonsofa.com/… – Nur

@devk in the post he's just passing raw queries to the database, I could do this for sure but the whole point of this test project was to learn Eloquent and its inner workings :) is there anyway to achieve the result without running raw queries? – Trubow

Well let's say you do Album::with('images')->get() and you only have 3 albums in db (with ids: 1, 2 and 3). Laravel behind the scenes executes next 2 queries: first select * from albums this returns the albums, puts ids in array ([1, 2, 3]) and then it executes select * from images where in alubm_id [1, 2, 3]. If you can tell me what SQL to write to limit 2nd query to 3 images per album, I will put it into query builder (I'm good at Laravel - especially Eloquent stuff, not that good with SQL). But I'm pretty sure there isn't an easy way to do this. – Nur

And the linked post uses as much eloquent and query builder as possible. Query builder provides nice methods for most of the capabilities of most of the SQLs. But not everything. Stuff like this @rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias} you will always need to use raw statements. – Nur

Odd... This is exactly how I do it in all of my projects, and it functions as expected; each parent (Album) has a relationship of children (Image) that's limited to X entries. Only exception is that I use limit() instead of take() in the ->with([...]) function. Maybe an issue with the DB type your using? I'm using MySQL, but I don't see where you specify what you're using. – Konikow

Answer 1

I feel you'd quickly run into an N+1 issue trying to accomplish this. Just do it in the collection that it returns:

Album::with('images')->get()->map(function($album) {
    $album->setRelation('images', $album->images->take(3));
    return $album;
});

Corvette

It turns out that if there are 20 albums, then there will be 20 additional queries. And how to do in one query? – Dryly

The with('images') eager loads the images for the albums which will fetch everything in 2 queries (one for albums and one for all images). – Corvette

with('images') actually makes it load all the models, can be achieved without using with('images') – Desquamate

Answer 2

For those who don't mind the N + 1 issue, and maybe even prefer it.

On the Album model, have a custom attribute that returns the limited relations, i.e.

class Album extends Model
{
    public function images(): hasMany
    {
        return $this->hasMany('App\Image');
    }

    public function getLimitedImagesAttribute()
    {
        return $this->images()->take(3)->get();
    }
}

In your controller:

class MainController extends Controller
{
    return Album::select('name')->get()->each->append('limited_images')
}

NOTE: This method will run N + 1 queries, where N is the number of albums in the database. This method may be preferred if the number of rows in the Album table is not too large, but the related image table is too large.

Misdeal

Answer 3

the take() eloquent method just adds 'limit' word at the end of the query. this type of query is more complex and isn't supported by vanilla eloquent.

fortunately, there is an additional package called eloquent-eager-limit, which helps with this problem. in order to make it work, install that package by using composer require staudenmeir/eloquent-eager-limit command and put use \Staudenmeir\EloquentEagerLimit\HasEagerLimit; line inside both parent and children model classes.

Embowed

The staudenmeir/eloquent-eager-limit package is part of Laravel now, since Laravel 11. See laravel-news.com/eager-load-limit – Mange

Answer 4

Laravel 11+ allows limiting the number of eager loading results per parent using window functions.

The staudenmeir/eloquent-eager-limit package has been merged into Laravel 11+ and eager loading limits are now supported natively.

Links:

• Package documentation says about merge into Laravel
  https://packagist.org/packages/staudenmeir/eloquent-eager-limit

• Eager Load Limit announcement
  https://laravel-news.com/eager-load-limit

Mange