php link 多条件查询,php-Laravel雄辩的内部联接具有多个条件

php-Laravel雄辩的内部联接具有多个条件

我有一个关于多个值的内部联接的问题。我确实在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是一列而不是字符串。 这是否有可能，还是我必须在哪里修复它。

提前致谢！

7个解决方案

55 votes

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

rama answered 2020-07-21T06:10:23Z

29 votes

您可以看到以下代码来解决问题

return $query->join('kg_shops', function($join)

{

$join->on('kg_shops.id', '=', 'kg_feeds.shop_id');

$join->where('kg_shops.active','=', 1);

});

或另一种解决方法

return $query->join('kg_shops', function($join)

{

$join->on('kg_shops.id', '=', 'kg_feeds.shop_id');

$join->on('kg_shops.active','=', DB::raw('1'));

});

Majbah Habib answered 2020-07-21T06:10:47Z

17 votes

因为您这样做的方式使其认为两者都是下面给出的代码中的联接条件：

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

});

}

因此，您应该删除第二行：

return $query->join('kg_shops', function($join)

{

$join->on('kg_shops.id', '=', 'kg_feeds.shop_id');

});

现在，您应该添加一个where子句，它应该像这样：

return $query->join('kg_shops', function($join)

{

$join->on('kg_shops.id', '=', 'kg_feeds.shop_id')->where('kg_shops.active', 1);

})->get();

The Alpha answered 2020-07-21T06:11:16Z

11 votes

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

Yagnesh bhalala answered 2020-07-21T06:11:32Z

8 votes

更多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;

希望对您有所帮助;)

ThangTD answered 2020-07-21T06:11:56Z

1 votes

您可以简单地添加多个条件，只需将它们添加为join闭包内的where()即可

->leftJoin('table2 AS b', function($join){

$join->on('a.field1', '=', 'b.field2')

->where('b.field3', '=', true)

->where('b.field4', '=', '1');

})

Kamlesh answered 2020-07-21T06:12:16Z

0 votes

这在政治上不正确，但可以

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

Uncoke answered 2020-07-21T06:12:35Z