使用DB::raw和雄辩的相同查询的不同结果

Question

在运行一个雄辩的联接查询时，我得到了一些意想不到的结果。我通过使用完全相同的查询得到两个不同的结果。一次运行DB::raw()，第二次运行雄辩。

在雄辩的查询中，匹配

where squad_user.leave_time >= seasons.start_time

将不包括在结果集中。匹配的用户。

or squad_user.leave is null

不过，将包括在内。

这是两个查询结果的唯一不同之处。原始查询实际上生成所需的结果集。

真正让我困惑的是，如果我检查查询日志，无论是Laravel的还是MySQL的，我在运行原始和雄辩的查询时都会得到完全相同的查询。

原始查询(运行雄辩查询时从查询日志获得的实际查询)

return \DB::select(\DB::raw('
        select users.*
        from users
        inner join squad_user on users.id = squad_user.user_id
        inner join seasons on squad_user.squad_id = seasons.squad_id
        where squad_user.join_time <= seasons.end_time
        and (squad_user.leave_time >= seasons.start_time or squad_user.leave_time is null)
        and seasons.id = :seasonId
    '),
    ['seasonId' => 3]
);

雄辩的质疑

return User::join('squad_user', 'users.id', '=', 'squad_user.user_id')
           ->join('seasons', 'squad_user.squad_id', '=', 'seasons.squad_id')
           ->where('squad_user.join_time', '<=', 'seasons.end_time')
           ->where(function ($query)
           {
               $query->where('squad_user.leave_time', '>=', 'seasons.start_time')
                     ->orWhereNull('squad_user.leave_time');
           })
           ->where('seasons.id', 3)
           ->get(['users.*']);

Laravel雄辩的查询日志

select `users`.*
from `users`
inner join `squad_user` on `users`.`id` = `squad_user`.`user_id`
inner join `seasons` on `squad_user`.`squad_id` = `seasons`.`squad_id`
where `squad_user`.`join_time` <= seasons.end_time
and (`squad_user`.`leave_time` >= seasons.start_time or `squad_user`.`leave_time` is null)
and `seasons`.`id` = 3
{"bindings":["seasons.end_time","seasons.start_time",3],"time":0.38,"name":"mysql"}

MySQL在雄辩的查询上的general_log

select `users`.*
from `users`
inner join `squad_user` on `users`.`id` = `squad_user`.`user_id`
inner join `seasons` on `squad_user`.`squad_id` = `seasons`.`squad_id`
where `squad_user`.`join_time` <= ?
and (`squad_user`.`leave_time` >= ? or `squad_user`.`leave_time` is null)
and `seasons`.`id` = ? 

MySQL在原始查询上的general_log

select users.*
            from users
            inner join squad_user on users.id = squad_user.user_id
            inner join seasons on squad_user.squad_id = seasons.squad_id
            where squad_user.join_time <= seasons.end_time
            and (squad_user.leave_time >= seasons.start_time or squad_user.leave_time is null)
            and seasons.id = ?

我很感激这里的任何指点，因为我非常迷茫。

Answer 1

where绑定第三个param，并通常将其作为字符串处理，除非您通过使用raw语句告诉它不要这样做。DB::raw或whereRaw将为您工作：

return User::join('squad_user', 'users.id', '=', 'squad_user.user_id')
       ->join('seasons', 'squad_user.squad_id', '=', 'seasons.squad_id')
       ->where('squad_user.join_time', '<=', DB::raw('seasons.end_time'))
       ->where(function ($query)
       {
           $query->where('squad_user.leave_time', '>=', DB::raw('seasons.start_time'))
                 ->orWhereNull('squad_user.leave_time');
       })
       ->where('seasons.id', 3)
       ->get(['users.*']);

Answer 2

由于LaravelVerian5.2，您还可以使用whereColumn来验证两个列是否相等(或者向该方法传递一个比较运算符)：

->whereColumn('squad_user.join_time', '<=', 'seasons.end_time')