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

Have you tried using `$query->limit(3)` in your first Closure attempt, instead of `take(3)`? — GiamPy, Mar 29 '17 at 15:20
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 — twigg, Mar 29 '17 at 15:22
This is 5.4 a fresh copy from yesterday (just playing around with it not a serious project) — twigg, Mar 29 '17 at 15:23
Alright this isn't easy. Here's a post about it. https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/ — DevK, Mar 29 '17 at 15:24
@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? — twigg, Mar 29 '17 at 15:27
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. — DevK, Mar 29 '17 at 15:44
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. — DevK, Mar 29 '17 at 15:48
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. — Tim Lewis, Mar 29 '17 at 16:41

score 19 · Accepted Answer · answered Mar 29 '17 at 16:48

19

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;
});

answered Mar 29 '17 at 16:48

Eric Tucker

6,144
1
22
36

It turns out that if there are 20 albums, then there will be 20 additional queries. And how to do in one query? – Ilya Degtyarenko Mar 01 '20 at 09:58
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). – Eric Tucker Mar 02 '20 at 17:21
with('images') actually makes it load all the models, can be achieved without using with('images') – Haseeb Zulfiqar Jun 08 '22 at 12:44

score 3 · Answer 2 · answered Oct 21 '22 at 12:18

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.

erbelion · Answer 3 · 2022-09-08T21:49:42.223

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.

Laravel Eloquent limit results for relationship

3 Answers3

Linked