使用DB :: raw和Eloquent使用同一查询的不同结果 [英] Different results using same query with DB::raw and Eloquent
问题描述
运行雄辩的联接查询时,我得到了一些意外的结果.通过使用完全相同的查询,我得到两个不同的结果.一个运行于DB :: raw(),第二个运行于Eloquent.
I'm getting some unexpected results when running an Eloquent join query. I get two different results from using the exact same query. One running with DB::raw(), the second with Eloquent.
在口才查询中,与
where squad_user.leave_time >= seasons.start_time
丢失,不会包含在结果集中.符合
are missing and will not be included in the result set. The users that matches the
or squad_user.leave is null
.
这是两个查询的结果之间的唯一区别.原始查询实际上会产生所需的结果集.
That's the only difference in the results from the two queries. The raw query actually produces the desired result set.
真的 使我感到困惑的是,如果我同时查询了Laravel和MySQL的查询日志,那么在同时运行raw和Eloquent查询时,我会得到完全相同的查询.
What really puzzles me is, if I check the query logs, both Laravel's and MySQL, I get the exact same query when running both the raw and Eloquent query.
原始查询(运行口才查询时从查询日志中获得的 actual 查询)
Raw query (the actual query i get from the query log when running the Eloquent query)
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` = ?
关于Raw查询的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 = ?
我会很感激此处,因为我很迷失.
I would appreciate any pointers here, as I am very lost.
推荐答案
where
绑定第3个参数,通常将其视为字符串,除非您不通过使用raw语句告诉它. DB::raw
或whereRaw
将为您工作:
where
binds 3rd param and treats it usually as a string, unless you tell it not to by using raw statement. DB::raw
or whereRaw
will work for you:
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.*']);
这篇关于使用DB :: raw和Eloquent使用同一查询的不同结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!