CODEWITHSUNDEEP
laravelwhere-clauselaravel-5.4
user1050544
user1050544

Reputation: 447

Laravel 5.4 - whereHas

We have an old application written in Laravel 5.2. We are currently trying to upgrade to 5.4 and the last thing standing in our way is a whereHas bug. Here are the raw sql queries:

# Laravel 5.4 Query:
select * from `jobs` where exists (select * from `channels` where `jobs`.`channel_id` = `channels`.`id` OR (`id` = 'RetroSnickers' or `channelid` like 'RetroSnickers' or `channelname` like 'RetroSnickers' or exists (select * from `users` where `channels`.`user_id` = `users`.`id` and (`email` like 'RetroSnickers' or `full_name` like 'RetroSnickers')))) and `jobs`.`deleted_at` is null limit 24 offset 0

# Laravel 5.2 Query:
select * from `jobs` where exists (select * from `channels` where `jobs`.`channel_id` = `channels`.`id` AND (`id` = 'RetroSnickers' or `channelid` like 'RetroSnickers' or `channelname` like 'RetroSnickers' or exists (select * from `users` where `channels`.`user_id` = `users`.`id` and (`email` like 'RetroSnickers' or `full_name` like 'RetroSnickers')))) and `jobs`.`deleted_at` is null limit 24 offset 0

This is the code that generates this raw sql. The code is the same in our 5.4 version as it is in the 5.2 version:

$this->query->whereHas('channel', function(Builder $query) use ($search) {

            $query->orWhere('id', '=', $search);
            $query->orWhere('channelid', 'like', '%'.$search.'%');
            $query->orWhere('channelname', 'like', '%'.$search.'%');

            $query->orWhereHas('user', function(Builder $subQuery) use ($search) {

                $subQuery->where(function(Builder $subQuery2) use ($search) {

                    $subQuery2->orWhere('email', 'like', '%'.$search.'%');
                    $subQuery2->orWhere('full_name', 'like', '%'.$search.'%');
                });
            });
        });

The only difference in the raw sql is bolded, but basically a difference between a mysql OR vs AND. An OR clause is generated in 5.4

Any idea why?

Upvotes: 0

Views: 889

Answers (1)

patricus
patricus

Reputation: 62228

This was a change that occurred in Laravel 5.3. From the 5.3 upgrade guide:

Eloquent scopes now respect the leading boolean of scope constraints. For example, if you are starting your scope with an orWhere constraint it will no longer be converted to normal where. If you were relying on this feature (e.g. adding multiple orWhere constraints within a loop), you should verify that the first condition is a normal where to avoid any boolean logic issues.

In 5.2, if your first where condition was an orWhere(), it would ignore the 'OR' part and 'AND' it with the rest of the conditions. As of 5.3, it respects the 'OR' part and will 'OR' the first condition with the rest of the conditions.

If you're expecting an 'AND', you need to change the first condition from orWhere() to just where():

$query->where('id', '=', $search);

Upvotes: 3

Related Questions