Three-way pivot table with Eloquent

replied 9 years ago

Just say, that maybe is better to have separated relations defined (belongsTo..)Or maybe creating a model for the pivot table.., Ralations separated one from each other and make couple queries better than doing that separated and use whereIn(I know that queries using large whereIn isn't best idea, but what you did I think is worse). Or maybe best is to use ->join()... I did same thing's but using couple queries or ->join() as I've already said

http://www.toptal.com/php/10-most-common-mistakes-php-programm....

Common Mistake #4: Performing queries in a loop

Last updated 1 year ago.

0

nickvleeuwen

replied 9 years ago

Hi Ekaitzastiz,

Thnx for your reaction, I know that my code is wrong, that's what im telling in my post. Im trying to find a solution for the three scenario's I typed above.

I tried making the pivot table an model and tried some things but I can't get it to work the way I told in the scenario's.

Can you show me an example how you would use the join in this case.

Last updated 1 year ago.

0

ekaitzastiz

replied 9 years ago

I can't find an older replay I did in this forum some month ago with some snippet. But reading your code and If we focus on the method name getTeamsWithUsersByRoles(), maybe you can use ->whereHas() (not best maybe, but better than that).

Here a fast idea or pseudo-code (Laravel >= 4.1.*) . You should decide using eager loading (with() or maybe load()) or better join(),leftJoin() or rightJoin() for a big result:

in Team.php model use

public function users()
{
return $this->belongsToMany('User','role_team_user','team_id','user_id'); //->withPivot('role_id')
}


public function roles()
{
return $this->belongsToMany('Role','role_team_user','team_id','role_id'); //->withPivot('user_id') if you need saving
}

...similar in all models with the relations you need you need it. I haven't tried appending the ->with('roles') in that relation. Can be an option. i don't know, I think, you need to think again, but I think that there are many solutions better than that one you used.

Team::with(['users','roles'])->whereHas('role_team_user',function($query)use($role_id){
                $query->where('role_team_user.role_id','=',$role_id);
            });

//rename whereHas('role_team_user as rtu',....if wanted

Or maybe the easiest, if you have a model of the pivot table:

TeamUserRole::with('users','teams')->where('role_id','=',$role_id)->get();

whereHas() see the api. http://laravel.com/api/4.1/ I repeat, not best, if you listen to queries, you will se 2 queries..or three depending relations. Sometimes, for complex or not paginated results i prefer using join, leftJoin,rightJoin. In paginated results the related item queries whereIn() is limited too, so I't not slow.

(role change is not very frequent (if is changed or updated, then clean the cache tag you are using), so maybe you can introduce chaching, have you ever used ->remember($minutes) or if you prefer cache:: and if there are a lot of queries or data to cache, maybe use memcached. If you need help tell me).

Last updated 1 year ago.

0

jarektkaczyk

replied 9 years ago

Solution

You can do it using something like this: https://github.com/jarektkaczyk/Eloquent-triple-pivot

It lets you do this:

// User
public function roles()
{
  return $this->tripleBelongsToMany('Role', 'Team');
}

// then
$user = User::with('roles');
$user->roles->first()->third; // Team model

However it doesn't scale, because you can't eager load 'third' models. (Maybe I'll work on that soon so it does eager load, but no time right now :/ )

Another solution, one that I would suggest for the time being, is this:

// custom model for pivot table (not Pivot model!)
// RoleTeamUser model
public function user()
{
  return $this->belongsTo('User');
}
public function role()
{
  return $this->belongsTo('Role');
}
public function team()
{
  return $this->belongsTo('Team');
}

// and for all 3 Team, User, Role this relation:
public function teamsRolesUsers()
{
  return $this->hasMany('TeamRoleUser');
}

Then you can eager load everything like this:

$user = User::with('teamsRolesUsers.team', 'teamsRolesUsers.role')->first();
$user->teamsRolesUsers->first()->team;
$user->teamsRolesUsers->first()->role;

It will execute 3 queries all, so not that much. However it's not the way for big data like sets, since those 3 queries will be all whereIn.

However, this way you have pairs team/role per user, what is rather cumbersome with simple belongsToMany relation.

Last updated 1 year ago.

0

nickvleeuwen

replied 9 years ago

thanks for the reply, think this should work. Will look into it tonight :)

Last updated 1 year ago.

0