How to paginate Eager Loaded relation in laravel

Question

I have a serious problem.

I have 3 models

Role Model:

<?php

class Role extends Eloquent{

/**
 * The database table used by the model.
 *
 * @var string
 */
protected $table = 'roles';



public function users()
{
    return $this->belongsToMany('User', 'user_roles');
}

}

User Model:

public function roles()
{
    return $this->belongsToMany('Role', 'user_roles');
}

Now In my controller I am getting users by selected role or if not selected I am getting all users related to all roles:

$roles = Role::with([
        'users' => function($query) use ($memtype)
        {

            $query->where('users.membertype', $memtype);
            $query->orderBy('users.name');
            $query->paginate(10);
        }
    ])->where('id', $role_id)->get();

If I don't want to filter by role I am using following code:

$roles = Role::with([
        'users' => function($query) use ($memtype)
        {

            $query->where('users.membertype', $memtype);
            $query->orderBy('users.name');
            $query->paginate(10);
        }
    ])->get();

In my view

I am trying to generate pagination links by following code,

@foreach($roles as $role)

@foreach($role->users as $user)
    {{ $user->name }}
    <br>
    {{ $user->email }}

@endforeach
{{ $role->users()->links() }}
@endforeach

But I am getting errors:

ErrorException

Call to undefined method Illuminate\Database\Query\Builder::links() (View: C:\xampp\htdocs\waytohouse\app\views\admin.blade.php)

Please help me to get out of this issue..

Also I tried Option1 in following in following link:

Laravel 4.1: How to paginate eloquent eager relationship?

But how to add constraints in the option1. In my case it is membertype? Thanks in advance!

Answer 1

There are a few things that make impossible, what you are trying to do (impossible with Eloquent relation methods obviously).

The way Eloquent handles eager loading and you using paginate makes the whole thing incorrect.

Let's check example without paginate:

select users.*, user_roles.user_id as pivot_user_id, user_roles.role_id as pivot_role_id
inner join user_roles on user_roles.user_id = users.id
where user_roles.role_id in ( .. some ids here .. );

then Eloquent will match returned users with their roles.

Now, with paginate we basically add limit and offset to the query. So here it goes:

select users.*, [...]
where user_roles.role_id in ( .. some ids here .. )
limit 10 offset 0;

This means that you just sliced number of rows to return to 10. 10 in total, not 10 per role.

That being said, you end up with unexpected and completely unreal results (unless, luckily there were only 10 or less users to return, but I wouldn't count on luck with this one).

---

There's another thing:

$role->users; // collection of eager loaded users

$role->users(); // BelongsToMany relation object

The latter is totally independent on previously eager loaded collection, and has nothing to do with anything that you would call in the with closure.

---

So, the only way to paginate related models is in fact using:

$users = $role->users()->paginate(10);

// for example:
$roles = Role::where(..)->get();
$roles->map(function ($role) {
  $role->users = $role->users()->paginate(10);
});

for each role. That means querying the db once for each role.

Also you need to let Laravel know, which role's users are paginated. I suppose ajax is the only method you would use to show this to the user, so ok. If not, it's cumbersome.

Another way would be loading everything and 'paginatin' the collection $role->users, however I definitely would not.