Different results using same query with DB::raw and Eloquent

eloquent, laravel, mysql, php, sql

Solution

`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.*']);

Problem

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. In the Eloquent query, the Users that matches the `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` will be included, however. That's the only difference in the results from the two queries. The raw query actually produces the desired result set. 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. 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] ); ``` Eloquent query ``` 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's Eloquent query 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` = 3 {"bindings":["seasons.end_time","seasons.start_time",3],"time":0.38,"name":"mysql"} ``` MySQL's general_log on the Eloquent query ``` 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's general_log on the Raw query ``` 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.

Original source