Laravel Eloquent whereRaw sql 子句在使用 OR 运算符时返回所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27193509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Eloquent whereRaw sql clause returns all rows when using OR operator
提问by lowkey
In Laraveli am using Eloquent ORMto fetch all users associated with a group.
在Laravel 中，我使用Eloquent ORM来获取与一个组关联的所有用户。
(It's a legacy database so it doesn't follow Laravel conventions unfortunately).
（这是一个遗留数据库，因此不幸的是它不遵循 Laravel 约定）。
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.
用户表有一个带有 GroupId 的列，该列是组表的外键，其中包含有关用户所属组的相关信息。
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.
我想选择与两个特定组关联的所有用户，按他们的名字选择。当我不在 SQL 语句的 whereRaw 子句中使用 OR 运算符时，它会起作用。在这种情况下，它只返回所有行，不管它们的名字。
If i remove the OR ..., the code works as intended and returns only users associated with the GroupId i query, in this case "KO011".
如果我删除 OR ...，代码将按预期工作并仅返回与 GroupId i 查询关联的用户，在本例中为“KO011”。
('GroupId's semantic meaning is just the Groups name)
('GroupId的语义就是Groups的名字)
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());
The sql log dump from when i query with the or operator is (undesired result, all rows):
我使用 or 运算符查询时的 sql 日志转储是（不需要的结果，所有行）：
[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"} []
回答by Jarek Tkaczyk
You must nest these wheres, also why do you want that whereRawthere?
你必须嵌套这些wheres，你为什么要在whereRaw那里？
->whereHas('Group', function($q)
{
$q->where(function ($q) {
$q->where('GroupId', 'KO11')->orWhere('GroupId', 'KO05');
});
})
Because whereHasadds something like where fk = ? and ....
因为whereHas添加了类似where fk = ? and ....
Other way would be:
其他方式是：
$q->whereRaw("(GroupId = 'KO11' OR GroupId = 'KO05')")
