How to draw shapes
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 get users by selected role or if not selected I get all users associated with 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 use the 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 opinion
I am trying to create links to pages 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 undefined method Illuminate \ Database \ Query \ Builder :: links () (View: C: \ xampp \ htdocs \ waytohouse \ app \ views \ admin.blade.php)
Please help me out of this problem ..
Also I tried Option1 in the following link:
Laravel 4.1: How to break up an eloquent impatient relationship?
But how do I add restrictions to option1. In my case, is it a member type? Thanks in advance!
source to share
There are a few things that make it impossible what you are trying to do (perhaps using the Luminance relationship methods).
The way Eloquent handles the uploaded upload, and you are using paginate
, is all wrong.
Let's check an 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 the return users
from theirs roles
.
Now, using paginate
, we basically add a request limit
and offset
to the request. So here it is:
select users.*, [...]
where user_roles.role_id in ( .. some ids here .. )
limit 10 offset 0;
This means that you just chopped the number of lines to go back to 10. only 10, not 10 per role .
So you get unexpected and completely unrealistic results (unless, fortunately, there are only 10 or fewer users left, but I wouldn't count on being lucky with that).
Another thing:
$role->users; // collection of eager loaded users
$role->users(); // BelongsToMany relation object
The latter is completely independent of the previously loaded collection and has nothing to do with what you might call in the closure with
.
So the only way to split into related models is actually 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. This means that you need to query the db once for each role.
Also you need to tell Laravel which user role is paginated. I suppose ajax is the only method you would use to show this to the user, so fine. If not, it's cumbersome.
Another way is to load everything and "paginatin" into a collection $role->users
, but I definitely wouldn't.
source to share
There is actually a way to select 10 users per user role using partitions (in PostgreSQL). This is the request:
SELECT * FROM (
SELECT *, rank() over (partition by role_id order by id) FROM users
) AS partitioned_users
WHERE rank > 10 AND rank <= 20
So SQL is not a problem. The only question is how to build a query using laravel Query Builder. Can be loaded without pagination as follows:
UserRole::with(['users' => function($query) {
$subQ = DB::table('users AS partitioned_users')->select('*')
->addSelect(DB::raw('rank() over (partition by user_role_id order by id desc)'));
/** @var Builder $query */
$query->from(DB::raw("(".$subQ->toSql().") AS users"))
->where('rank', '<=', 10)
->setBindings(array_merge_recursive($subQ->getBindings(), $query->getBindings()));
})->all()
So it will load all user roles and start loading the first 10 users for each user role.
But I haven't figured out page fragmentation yet. We need to create a custom pagination that will use this SQL:
WHERE rank > :offset AND rank <= :limit
instead of the standard one:
LIMIT :limit OFFSET :offset
I'll tell you about it when I find out.
source to share