在运行Eloquent连接查询时得到了一些意想不到的结果。使用完全相同的查询,我得到两个不同的结果。一个使用DB::raw(),另一个使用Eloquent。
在Eloquent查询中,匹配
的Users where squad_user.leave_time >= seasons.start_time
缺失,不会包含在结果集中。匹配
的用户 or squad_user.leave is null
将包括在内。
这是两个查询结果的唯一区别。原始查询实际上生成所需的结果集。
真正让我困惑的是,如果我检查查询日志,无论是Laravel的还是MySQL的,当运行raw和Eloquent查询时,我得到完全相同的查询。
原始查询(运行Eloquent查询时从查询日志中获得的实际查询)
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的Eloquent查询日志
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关于Eloquent查询的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关于Raw查询的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 = ?
where
绑定第三参数并通常将其视为字符串,除非您通过使用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.*']);
从Laravel version 5.2开始,您还可以使用whereColumn
来验证两列是否相等(或将比较操作符传递给方法):
->whereColumn('squad_user.join_time', '<=', 'seasons.end_time')