LEFT OUTER JOIN using eloquent with AND operator

i have 3 panel admin,master and client.

admin activates all sports

but master deactivate some sports, so client can not see sport which is deactivated by master (is parent of client)

I have two tables 'sports' and in_active_sports

sports

id
name
is_active

in_active_sports

id
user_id
is_active (always false)

I want to merge status of right table with left table for specific user.

meanwhile if right table has status then replace with left table's status, otherwise keep left table's status

$sports = Sport::query()
                ->join('in_active_sports as iasp', 'sports.id', '=', 'iasp.sport_id AND iasp.user_id = 2', 'left outer')
                ->select('sports.*','iasp.is_active as is_active')
                ->get();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'iasp.sport_id AND iasp.user_id = 2' in 'on clause' (SQL: select `sports`.*, `iasp`.`is_active` as `is_active` from `sports` left outer join `in_active_sports` as `iasp` on `sports`.`id` = `iasp`.`sport_id AND iasp`.`user_id = 2`)"

I want to use eloquent because i am using casting attribute.

s4muel

5 years ago

Level 50

i havent dig deep enough, but this part is not correct AND iasp.user_id = 2, shouldnt it be like this?:

$sports = Sport::query()
    ->join('in_active_sports as iasp', 'sports.id', '=', 'iasp.sport_id', 'left outer')
    ->select('sports.*','iasp.is_active as is_active')
    ->get();

or with the user_id condition:

$sports = Sport::query()
    ->join('in_active_sports as iasp', 'sports.id', '=', 'iasp.sport_id', 'left outer')
    ->where('iasp.user_id', 2)
    ->select('sports.*','iasp.is_active as is_active')
    ->get();

oh, and you probably dont need to specify the left outer option if you use lefJoin() instead of join()

$sports = Sport::query()
    ->leftJoin('in_active_sports as iasp', 'sports.id', '=', 'iasp.sport_id')
    ->select('sports.*','iasp.is_active as is_active')
    ->get();

MichalOravec

5 years ago

Best Answer

Level 75

$userId = 2;

$sports = Sport::select('sports.*', 'iasp.is_active AS is_active')->leftJoin('in_active_sports AS iasp', function ($join) use ($userId) {
    $join->on('sports.id', '=', 'iasp.sport_id')->where('iasp.user_id ', $userId);
})->get();

Documentation: https://laravel.com/docs/7.x/queries#joins (look for Advanced Join Clauses and Left Join / Right Join Clause)

mungpara

5 years ago

Level 2

Thanks you @michaloravec

Working now

Please or to participate in this conversation.