Laravel Eloquent inner join with multiple conditions

Question

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.

score 86 · Accepted Answer · answered Dec 24 '13 at 17:58

86

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();

answered Dec 24 '13 at 17:58

rama

892
6
2

You can add the where clause inside the join, so eloquent it will think that "1" is not a column, as Kamlesh says. – Ricard Espinàs Llovet May 28 '21 at 09:33

score 68 · Answer 2 · answered Aug 01 '16 at 09:51

68

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'));
});

answered Aug 01 '16 at 09:51

Majbah Habib

8,058
3
36
38

1

If i'm print query log then it is showing `AND` between both condition. But how I can use `or` instead of `AND` – er.irfankhan11 Jun 09 '17 at 06:38
2

Use it. $join->orOn('kg_shops.active','=', DB::raw('1')); – Majbah Habib Jun 11 '17 at 09:34
1

If the 'active' field contains a string, you have to use DB::raw("'YES'") instead of DB::raw('YES'). – Anees Sadeek Aug 15 '19 at 07:34

score 32 · Answer 3 · answered Jun 16 '18 at 08:51

//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);

The Alpha · Answer 4 · 2014-04-30T06:02:08.550

23

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();

edited Apr 30 '14 at 06:02

answered Dec 22 '13 at 18:12

The Alpha

143,660
29
287
307

Hi Sheikh,Thanks for the answer but I tried that. Gives me the following result. PHP Fatal error: Call to undefined method Illuminate\Database\Query\JoinClause::where() in /app/models/Feeds.php on line 35 – Wouter Neuteboom Dec 23 '13 at 08:50
How can I add more than one join with OR condition? like join On this OR join ON that? – Saani Aug 04 '17 at 09:58
`$join->on()->orOn()`. – The Alpha Aug 04 '17 at 12:33

score 11 · Answer 5 · answered Apr 19 '19 at 08:20

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');
})

ThangTD · Answer 6 · 2016-04-25T10:08:41.850

10

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 ;)

edited Apr 25 '16 at 10:08

answered Apr 25 '16 at 08:16

ThangTD

1,586
17
16

Instead of using `array_map` you can do: `$linkIds = $links->lists('id', 'id')` Also if you have eloquent models using `hasMany()` with `pivot()` works well too. – Ash Apr 25 '16 at 08:23
ok very thank for your suggest, I have not found it for a long :( – ThangTD Apr 25 '16 at 08:25
Sometimes things may not be documented but I tend to look at the source code on [Github](https://github.com/laravel/framework/tree/5.2/src/Illuminate) or the [API documentation](https://laravel.com/api/5.2/) – Ash Apr 25 '16 at 08:29
:D me too, I will try to reduce unnecessary codes like this, actually I lost time to dig a function like you gave but quited :( – ThangTD Apr 25 '16 at 08:33
Hey @ash, the `->lists()` is deprecated on 5.2. Use `->pluck()` instead, but actually I gave tries with pluck for nothing as my expected result. – ThangTD Apr 25 '16 at 08:35
1

It's just an alias for `pluck` it doesn't work once you have used `get`; so in your case `$linkIds = $user->links()->pluck('id')` – Ash Apr 25 '16 at 08:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110127/discussion-between-thangtd-and-ash). – ThangTD Apr 25 '16 at 10:09

score 6 · Answer 7 · answered Sep 03 '19 at 06:22

6

This is not politically correct but works

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

answered Sep 03 '19 at 06:22

Uncoke

1,832
4
26
58

pirix · Answer 8 · 2023-04-27T07:27:01.723

0

this is it works. You don't have to use $param. I wrote it this way as an example. If you want, you can just write the value in the condition.

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

edited Apr 27 '23 at 07:27

answered Apr 27 '23 at 07:24

pirix

1
1

Laravel Eloquent inner join with multiple conditions

8 Answers8

Linked