Laravel Eloquent inner join with multiple conditions

I have a question about inner joins with multiple on values. I did build my code like this in laravel.

public function scopeShops($query) { return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); // $join->on('kg_shops.active', '=', "1"); // WRONG // EDITED ON 28-04-2014 $join->on('kg_shops.active', '=', DB::raw("1")); }); } 

Only problem is, it gives this outcome:

Column not found: 1054 Unknown column '1' in 'on clause' (SQL: select `kg_feeds`.* from `kg_feeds` inner join `kg_shops` on `kg_shops`.`id` = `kg_ feeds`.`shop_id` and `kg_shops`.`active` = `1`) (Bindings: array ( )) 

As you can see, the multiple conditions in the join go fine, but it thinks the 1 is a column instead of a string. Is this even possible, or do I have to fix it in the where.

Thanks in advance!

7 Answers

return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); }) ->select('required column names') ->where('kg_shops.active', 1) ->get(); 

You can see the following code to solved the problem

return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); $join->where('kg_shops.active','=', 1); }); 

Or another way to solved it

 return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); $join->on('kg_shops.active','=', DB::raw('1')); }); 

//You may use this example. Might be help you... $user = User::select("users.*","items.id as itemId","jobs.id as jobId") ->join("items","items.user_id","=","users.id") ->join("jobs",function($join){ $join->on("jobs.user_id","=","users.id") ->on("jobs.item_id","=","items.id"); }) ->get(); print_r($user); 

Because you did it in such a way that it thinks both are join conditions in your code given below:

public function scopeShops($query) { return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); $join->on('kg_shops.active', '=', "1"); }); } 

So,you should remove the second line:

return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id'); }); 

Now, you should add a where clause and it should be like this:

return $query->join('kg_shops', function($join) { $join->on('kg_shops.id', '=', 'kg_feeds.shop_id')->where('kg_shops.active', 1); })->get(); 

You can simply add multiple conditions by adding them as where() inside the join closure

->leftJoin('table2 AS b', function($join){ $join->on('a.field1', '=', 'b.field2') ->where('b.field3', '=', true) ->where('b.field4', '=', '1'); }) 

More with where in (list_of_items):

 $linkIds = $user->links()->pluck('id')->toArray(); $tags = Tag::query() ->join('link_tag', function (JoinClause $join) use ($linkIds) { $joinClause = $join->on('tags.id', '=', 'link_tag.tag_id'); $joinClause->on('link_tag.link_id', 'in', $linkIds ?: [-1], 'and', true); }) ->groupBy('link_tag.tag_id') ->get(); return $tags; 

Hope it helpful ;)

This is not politically correct but works

 ->leftJoin("players as p","n.item_id", "=", DB::raw("p.id_player and n.type='player'"))