[php] 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!

This question is related to php laravel eloquent

The answer is


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

This is not politically correct but works

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

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

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


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

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to eloquent

Eloquent: find() and where() usage laravel How to select specific columns in laravel eloquent Laravel Eloquent where field is X or null Laravel Eloquent limit and offset laravel collection to array Eloquent get only one column as an array Laravel 5.2 - Use a String as a Custom Primary Key for Eloquent Table becomes 0 Laravel 5.2 - pluck() method returns array Eloquent ORM laravel 5 Get Array of ids eloquent laravel: How to get a row count from a ->get()