CODEWITHSUNDEEP
phplaravellaravel-4eloquent
Volatil3
Volatil3

Reputation: 14978

Laravel Eloquent using WHERE instead of AND in Joins

I have the following query:

select i.name,i.id itemId,its.item_id from items i
LEFT JOIN items_subscribers its
ON i.id = its.item_id
and its.user_id = 1

Which I transformed into Eloquent Way like this:

$records = Item::where('items_subscribers.user_id',$user_id)
            ->leftJoin('items_subscribers','items.id','=','items_subscribers.item_id')
            ->select(['items.name','items.id','items_subscribers.item_id as selected_item_id'])->get();

When I run print_r(DB::getQueryLog()); it prints following query:

select `items`.`name`, `items`.`id`, `items_subscribers`.`item_id` as `selected_item_id` from `items` left join `items_subscribers` on `items`.`id` = `items_subscribers`.`item_id` where `items_subscribers`.`user_id` = ?

The query yields nothing. I want to use AND instead of WHERE. How'd I do this?

I am using Laravel 4.2

Upvotes: 0

Views: 183

Answers (2)

lukasgeiter
lukasgeiter

Reputation: 152860

Try using a closure and adding the condition in there:

$records = Item::leftJoin('item_subscribers', function($join) use ($user_id){
        $join->on('items.id','=','items_subscribers.item_id');
        $join->where('items_subscribers.user_id', '=', $user_id);
    })
    ->select(['items.name','items.id','items_subscribers.item_id as selected_item_id'])
    ->get();

Upvotes: 1

Gal Sisso
Gal Sisso

Reputation: 1959

Just add another on method at the leftJoin part instead of using the where method. - https://stackoverflow.com/a/26714765/3208719

Upvotes: 0

Related Questions