Different results using same query with DB::raw and Eloquent
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.*']);
Since Laravel Verion 5.2 you can also use whereColumn
to verify that two columns are equal (or pass a comparison operator to the method):
->whereColumn('squad_user.join_time', '<=', 'seasons.end_time')