Laravel/Eloquent: condition on a pivot table

Question

I have 3 tables 'users', 'teams' and 'teams_users' (a pivot table). The pivot table contains a boolean field 'kicked'. I didn't find the way to get the list of teams which the user has not been kicked from. My code looks like this:

$teams = Team::withCount('users')
               ->where($params)
               ->orderBy(DB::raw('users_count / max_members'), 'desc')
               ->orderBy('team_id', 'desc')
               ->has('users', '<', DB::raw('max_members'));

$user = app('auth')->user();
if (isset($user)) {
    // Here add the condition to exclude teams the user has been kicked from
}

Possible duplicate of [Filtering pivot table data with Laravel models](https://stackoverflow.com/questions/20759891/filtering-pivot-table-data-with-laravel-models) — fubar, Apr 09 '18 at 22:55

Angelo · Accepted Answer · 2018-04-10T11:19:10.440

6

Digging the documentation, I found this:

if (isset($user)) {
    $teams->whereDoesntHave('users', function ($query) use ($user) {
        $query->where("is_kicked", "=", '1')
              ->where('teams_users.user_id', '=', $user->id);
    });
}

It generates a 'NOT EXISTS' condition in the SQL query. Works perfectly!

edited Apr 10 '18 at 11:19

answered Apr 10 '18 at 11:03

Angelo

198
1
14

Why the DB::raw? – Nathan Heffley Apr 10 '18 at 11:07
@NathanHeffley Indeed it is useless – Angelo Apr 10 '18 at 11:18
1

if this solved your problem, even though it's your own answer you should accept it. Help out the people who stumble across this in the future a little bit. – Nathan Heffley Apr 11 '18 at 19:12

score 5 · Answer 2 · answered Apr 10 '18 at 04:29

To start off, by convention your pivot should be named team_user and have team_id and user_id columns (it can have extras as well of course).

Next you should set up your relationships inside the models if you haven't yet. Use the belongsToMany() relationship inside your User model to be able to fetch teams like this

$user->teams

then you can simply do this

$user->teams()->where('kicked', false)->get()

more detail on relationships here https://laravel.com/docs/5.6/eloquent-relationships (see Many to Many for pivot table)

Thank you, you put me on the right path. Actually, I used `$teams->whereDoesntHave('users', function ($query){});` which filters my query the way I want. — Angelo, Apr 10 '18 at 11:05

Laravel/Eloquent: condition on a pivot table

2 Answers2