[5.4] Is it possible to paginate a relation?

[GertjanRoke]

• Laravel Version: 5.4.23
• PHP Version: 7.1.5
• Database Driver & Version: MariaDB v10.2.6

Description:

I was playing around with pagination and stumbled to this problem of not being able to paginate my eager loaded relations on a way that looked obvious to me.

So when I eager loaded my relation with the load() method, i thought it was possible to also add the pagination within the callback. But it turns out it those not work this way.

Maybe its something no one ever though about or is it impossible.

Could someone check this out? I tried to locate where it goes wrong but can't find the correct location for it, because of all the magic methods

Steps To Reproduce:

This is the code that I expected to work, but did not.

$tagGroup->load(['tags' => function($query) {
    $query->withCount('groups')->paginate();
}]);

With a small portion of my model for more information:

class TagGroup extends Model
{
    protected $fillable = ['name'];

    public function tags()
    {
        return $this->belongsToMany(Tag::class);
    }
}

class Tag extends Model
{
    protected $fillable = ['name'];

    protected $perPage = 10;

    public function groups()
    {
        return $this->belongsToMany(TagGroup::class);
    }
}

[themsaid]

Yes you can't do this, it actually doesn't make any sense to paginate while eager loading.

[Francismori7]

Actually it does make sense. A user could have thousands of orders.

Workaround right now is to call the model directly and add the where clauses yourself.

HOWEVER, in your code, you use withCount('groups') and then try to paginate that, which is not possible at all since the withCount method just counts ALL the data, you are not trying to paginate the tags relationship in this case.

[GertjanRoke]

Hello @Francismori7,

Your correct about the withCount() method, but that was just in my code and did not saw a point to remove it.

I want it this way so I have 1 variable within my view that I can access the tagGroup on but also the tags that are related to the given group. So I worked around it on this way below right now, but I thought the way I showed before was a more beautiful way.

$tagGroup->setRelation('tags', $tagGroup->tags()->withCount('groups')->paginate());

[Francismori7]

@GertjanRoke Makes more sense like this.

I kind of disagree, because in the load call you had, it's hard to see what you are trying to paginate from a simple lookup.

[themsaid]

I don't seem to understand the use case here, what are you using this eager loaded relationship for?

[GertjanRoke]

My use case is that I have a page in my cms system that shows some information about the tagGroup but also show a table that displays all the tags. In this case why use 2 variables if you could also get al the data from the $tagGroup variable.

For me and mabye for more people it can be handy to use as less variables as needed when the are already related.

So in my case a tagGroup can have many tags, so I would like to use $tagGroup->tags, because I already have a variable $tags to use for a select element within a form on that page. That contains all the tags that are not linked to the given tagGroup.

But I can also see a ise case in a way of showing al your Threads and you would like to show the total amount of pages full of replies.

[morloderex]

@GertjanRoke I guess you can just paginate the collection for this?

[GertjanRoke]

@morloderex about which use case are you talking about?

[morloderex]

@GertjanRoke I'm thinking eager load the relation and paginate the collection that got returned?

[GertjanRoke]

But then you don't have all the values like the current_page or from which item untill which item the current page is. Because I'm building a custom table with Vue its handy to have all of that information for pagination

[GertjanRoke]

@themsaid, What is the reason we can't use the paginate() method within a eager load callback? Tell me if i'm wrong, but in de callback your still working with the query builder that also has the paginate function in it. But you can not use it, but outside the callback is oke.

Maybe someone thought it throw, I would like to hear why.

[arjasco]

My wild guess is it's probably down to the same reason why you can't use a limit clause when eager loading because it uses a where in on the ids.

Eg. 10 Users each with 30 orders, but you only want to return the last 5 most recent orders for each user, then it's not going to work with a limit as you will only get 5 at most rows.

[GertjanRoke]

Understood! I will close the issue