具有多种条件的雄辩式内部连接

我有一个关于使用多个 on 值的内部连接的问题。 我确实在 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"));


});
}

唯一的问题是，它给出了这样的结果:

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

正如您所看到的，联接中的多个条件运行良好，但是它认为 1是一个列而不是一个字符串。这有可能吗，还是我得把它修好。

先谢谢你！

167155 次浏览
小开

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();
小开
最佳答案 
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();
小开

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

小开

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

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'"))

提交答案