php - Laravel Eloquent whereRaw sql 子句在使用 OR 运算符时返回所有行

Question

在Laravel我正在使用 Eloquent ORM获取与组关联的所有用户。

(这是一个遗留数据库，所以不幸的是它不遵循 Laravel 约定)。

users 表有一个带有 GroupId 的列，它是 Group 表的外键，其中包含有关用户所属组的相关信息。

我想选择与两个特定组关联的所有用户，按他们的名字选择。当我不在 SQL 语句的 whereRaw 子句中使用 OR 运算符时，它会起作用。在这种情况下，它只返回所有行，不管它们的名称。

如果我删除 OR ...，代码将按预期工作并仅返回与我查询的 GroupId 关联的用户，在本例中为“KO011”。

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

当我使用 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"} []

当我只查询一组时(正确结果):

[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"} []

Answer 1

你必须嵌套这些 wheres，还有你为什么要在那里放置 whereRaw？

->whereHas('Group', function($q)
{
    $q->where(function ($q) {
       $q->where('GroupId', 'KO11')->orWhere('GroupId', 'KO05');
    });
})

因为 whereHas 添加了类似 where fk = ?和...。

其他方式是:

$q->whereRaw("(GroupId = 'KO11' OR GroupId = 'KO05')")