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 16 · Accepted Answer · answered Mar 29 '17 at 16:48

16

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

5,536
1
21
33

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

score 0 · Answer 2 · answered May 23 '22 at 10:41

The accepted answer is very inefficient because it fetches all related items and then filters them afterwards. It's much better to do the limiting in the DB query, like this:

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

Make sure you get those square brackets right!

Laravel Eloquent limit results for relationship

2 Answers2

Linked