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

Question

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.

Answer 1

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