Laravel雄辩的whereRaw sql子句在使用OR运算符时返回所有行 [英] Laravel Eloquent whereRaw sql clause returns all rows when using OR operator
问题描述
在 Laravel 中，我正在使用
In Laravel i am using Eloquent ORM to fetch all users associated with a group.
(这是一个旧数据库，因此很遗憾，它没有遵循Laravel约定).
(It's a legacy database so it doesn't follow Laravel conventions unfortunately).
users表具有一列，其中GroupID是Group表的外键，GroupId具有与用户所属组相关的信息.
The users table has a column with a GroupId which is a Foreign Key to a Group table with the associated information on the group which the users belongs to.
我要选择与两个特定组相关联的所有用户，并按其名称进行选择.当我在SQL语句的whereRaw子句中不使用OR运算符时，它将起作用.在这种情况下，它只返回所有行，而不管它们的名字.
I want to select all users associated with two specific groups, selected by their name. And it works when i DO NOT use an OR operator in the whereRaw clause of the SQL statement. In that case it just returns all rows, despite their names.
如果我删除OR ...，则代码将按预期工作，并且仅返回与GroupId i查询关联的用户，在这种情况下为"KO011".
If i remove the OR ..., the code works as intended and returns only users associated with the GroupId i query, in this case "KO011".
("GroupId的语义含义只是网上论坛的名称)
('GroupId's semantic meaning is just the Groups name)
User::select(array('name','surname','GroupId'))->with(array('Group'=> function($q)
{
$q->select(array('Id','GroupId', 'GroupDescription'));
}))->whereHas('Group', function($q)
{
$q->whereRaw("GroupId = 'KO11' OR GroupId = 'KO05'");
})->get());
当我使用or运算符查询时的sql日志转储为(不需要的结果，所有行):
The sql log dump from when i query with the or operator is (undesired result, all rows):
[2014-11-28 17:12:40] local.INFO: select `name`, `surname`, `GroupId` from `Users` where (select count(*) from `Group` where `Users`.`GroupId` = `Group`.`Id` and GroupdId = 'KO11' or GroupId = 'KO05') >= 1 {"bindings":[],"time":182.84,"name":"dbname"} []
[2014-11-28 17:12:40] local.INFO: select `Id`, `GroupId`, `GroupDescription` from `Group` where `Group`.`Id` in (0, 12, 28, 9, 3, 32, 4, 2, 1, 16, 31, 13, 18, 10, 33, 29, 5, 11, 8, 21, 19, 20, 14, 30, 25, 22, 6, 23, 7, 15, 48, 17, 26, 24, 157, 52, 27, 51, 47, 50, 158, 134, 104, 105, 106, 118, 154, 103, 96, 107, 101, 108, 146, 102, 109, 100, 98, 99, 97, 95, 174, 94, 93, 92, 91, 90, 89, 88, 87, 86, 85, 84, 149, 183, 110, 184, 170, 111, 150, 114, 138, 113, 112, 159, 145, 121, 115, 140, 176, 117, 147, 135, 116, 139, 155, 148, 169, 164, 165, 166, 161, 185, 168, 172, 177, 167, 178, 180, 179, 191, 173, 193, 188, 175, 192, 187) {"bindings":[0,12,28,9,3,32,4,2,1,16,31,13,18,10,33,29,5,11,8,21,19,20,14,30,25,22,6,23,7,15,48,17,26,24,157,52,27,51,47,50,158,134,104,105,106,118,154,103,96,107,101,108,146,102,109,100,98,99,97,95,174,94,93,92,91,90,89,88,87,86,85,84,149,183,110,184,170,111,150,114,138,113,112,159,145,121,115,140,176,117,147,135,116,139,155,148,169,164,165,166,161,185,168,172,177,167,178,180,179,191,173,193,188,175,192,187],"time":55.44,"name":"dbname"} []
并且当我仅查询一组(正确结果)时:
and when i only query with one group (correct results):
[2014-11-28 17:57:26] local.INFO: select `name`, `surname`, `GroupId` from `Users` where (select count(*) from `Group` where `Users`.`GroupId` = `Group`.`Id` and GroupId = 'KO11') >= 1 {"bindings":[],"time":93.18,"name":"dbname"} []
[2014-11-28 17:57:26] local.INFO: select `Id`, `GroupId`, `GroupDescription` from `Group` where `Group`.`Id` in (3) {"bindings":[3],"time":405.02,"name":"dbname"} []
推荐答案
您必须嵌套这些wheres，为什么还要在其中嵌套whereRaw?
You must nest these wheres, also why do you want that whereRaw there?
->whereHas('Group', function($q)
{
$q->where(function ($q) {
$q->where('GroupId', 'KO11')->orWhere('GroupId', 'KO05');
});
})
因为whereHas添加了类似where fk = ? and ...的内容.
Because whereHas adds something like where fk = ? and ....
其他方式是:
$q->whereRaw("(GroupId = 'KO11' OR GroupId = 'KO05')")
这篇关于Laravel雄辩的whereRaw sql子句在使用OR运算符时返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
