Filter model, then join with many-to-many relation

Томица Кораћ Published at Dev

Томица Кораћ

I have two models, User and Training. They have many-to-many relationship with additional pivot fields like is_speaker and is_creator. I want to:

Get all the users with specified column value, for example: SELECT * FROM users WHERE business_unit = Finance;
Get the associated trainings where the pivot column value is is_speaker = true;

In other words, a visitor searches for all the trainings whose (first) speaker belongs to the specified business unit.

This is what I've tried so far:

$bu_users = $this->user->where( 'business_unit', $business_unit )->get();
$speakerTrainings = $bu_users->trainings()->wherePivot( 'is_speaker', true )->get();
dd( $speakerTrainings );
// Returns: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause' (SQL: select * from `trainings` where (select count(*) from `users` inner join `training_user` on `users`.`id` = `training_user`.`user_id` where `training_user`.`training_id` = `trainings`.`id` and `business_unit` = Finance and `users`.`is_active` = 1) >= 1 and `pivot` = is_speaker)

$bu_trainings = $this->training->with( 'users' )
    ->where( 'business_unit', '=', $business_unit )
    ->wherePivot( 'is_speaker', true)
    ->get();
dd( $bu_trainings );
// Returns: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'business_unit' in 'where clause' (SQL: select * from `trainings` where `business_unit` = Finance and `pivot` = is_speaker)

lukasgeiter

You're close. Now we kind of need to mix the two queries you have and add a bit of whereHas

Try this (this code returns all the users who belong to the specified business unit and are also speakers on any training):

$users = $this->user
         ->whereHas('trainings', function($q){
             $q->where('training_user.is_speaker', true);
         })
         ->where('business_unit', $business_unit)
         ->get();

This code will return all the trainings which have at least one speaker from the specified business unit:

$trainings = $this->training->whereHas( 'users', function( $q )
{
    $business_unit  = Input::get( 'bu' );
    $q->where( 'training_user.is_speaker', true );
    $q->where( 'business_unit', $business_unit );
})->get;

Edit

I know this isn't ideal but it's the only thing I can think of that works

First, for ease of use, define a speakers relation

public function speakers(){
    return $this->belongsToMany('User')->wherePivot('is_speaker', true);
}

Then first, eager load all speakers, filter the trainings them afterwards (edited):

$business_unit = Input::get('bu');
$trainings = $this->training->has('speakers')->with('speakers')->get();
$trainings = $trainings->filter(function($training) use ($business_unit){
    return $training->speakers->sortBy('training_user.id')->first()->business_unit == $business_unit;
});

Collected from the Internet

Please contact [email protected] to delete if infringement.