Laravel Eloquent triple table relation

Nothing here helps guide you:

https://laracasts.com/discuss?q=3+tables

https://laracasts.com/discuss?q=three+tables

https://laracasts.com/discuss/channels/laravel/relating-3-models

I've tried nearly all commented in those posts but i get stuck on retrieving the badges for a user in a particular team.

Users have different badges on each team.

So i would like to retrieve $user->team1->badges and $user->team2->badges; Something like: $user->badges->where('team_id, 1)->get();

But i don't know if its the most efficient way.

If you have only one badge per user/team, I suggest this way: Tables: users teams badges (user_id, team_id, badge_number)

Use badges as pivot table between user and team, but create a model for the pivot table to retrieve badge easily as it seems critical fpr your application.

With the links given by @jlrdw and the following, you should be able to do it : https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

Have u tried MorphMany option using "able" option.

Thanks @Vilfago but i have multiple badges for each team.

They are achievement badges, so every team has multiple. Many of them will be common in various teams but there are also custom badges for each team.

I need to access:

• $team->badges display achievable badges in a team.
• $user->badges(team) display all badges achieved by the user in a team.

Thanks @manshu but i don't get how morphMany is going to help in this case.

Ok. From my perspective, don't try to link all of that at once.

• User have many team, set this relations (Many to Many with user_team table)
• User have many badges, set this one (Many to Many with badge_user table)
• Team have many badges, set this one (Many to Many with badge_team table, or "One to Many" if a badge is always specific to a team).

I don't think you will able to use " Has Many Through" due to the many to many relations, but you should be able to retrieve data with nested eager loading.

//Retrieve user badges:
$user = User::with('badges')->find($userid);

//Retrieve user badges related to each team
$user = User::with('teams.badges')->find($userid);

//Retrieve all users of a team
$team = Team::with('users')->find($teamid);

//Retrieve all users of a team with badges
$team = Team::with('users.badges')->find($teamid);

//display achievable badges in a team
$team = Team::with('badges')->find($teamid);

//display all badges achieved by the user in a team - hard one
$user = User::with(['teams.badges' function ($q) {
        $q->where('teams.id', $teamid);}])
    ->find($userid); //not sure it will work

//Retrieve all your data
$users = User::with('teams.badges')->get();
$teams = Team::with('users.badges')->get();
badges = Badge::with('teams.users')->get();

And you can add more constraint if needed : https://laravel.com/docs/5.6/eloquent-relationships#constraining-eager-loads

Nice answer thanks! I had already tested has many through without success but i'll try nested eager loading with that table structure, seems the way to go.