Laravel 5.8 Eloquent doesn't work as intended despite the raw dumped query from it works

Question

I'm having an issue with Eloquent querying things different than if I'm using the raw query, despite it returning the exact same raw query when I dump it with toSql()

This should return 3 rows, but instead returns 40

$equipment = Equipment::where(["class_id" => $this->classId, "class_id2" => $this->classId, "class_id3" => $this->classId])
                    ->orWhere(["class_id" => 0, "class_id2" => 0, "class_id3" => 0])
                    ->where("type_id", ">", "7")
                    ->get();

This returns 3 and works as expected

$equipment = \DB::table("equipment")
                        ->whereRaw("(`class_id` = ".$this->classId." and `class_id2` = ".$this->classId." and `class_id3` = ".$this->classId.") 
                                    or (`class_id` = ".$this->classId." or `class_id2` = ".$this->classId." or `class_id3` = ".$this->classId.") 
                                    and `type_id` > 7")
                        ->get();

The exact raw query Eloquent dumps is this:

select * 
from `equipment` 
where (`class_id` = 14 and `class_id2` = 14 and `class_id3` = 14) 
   or (`class_id` = 14 or `class_id2` = 14 or `class_id3` = 14) 
  and `type_id` > 7

When running this in Navicat, it also returns 3 rows.

What am I missing here?

Qirel · Accepted Answer · 2019-07-27 20:13:07Z

1

You should be more explicit about your groups of conditions, which you can do by using an anonymous function as the argument to the first where(), with the argument of $query (which will be the querybuilder object itself).

$classID = 14;
$equipment = Equipment::where(function($query) use ($classID) {
                                  $query->where('class_id', $classID)
                                        ->orWhere('class_id2', $classID)
                                        ->orWhere('class_id3', $classID);
                        })->where("type_id", ">", "7")
                        ->get();

The query you currently have doesn't make that much sense, as the OR conditions will always be true when the AND condition is, so we can remove the first part of it, thereby simplifying the query.

You can debug your queries by using toSql() instead of get(), which will print the final SQL string. Had you done that here, you'd see that the conditions becomes something like WHERE .. OR .. AND .., instead of WHERE (.. OR ..) AND ...

edited Jul 27, 2019 at 20:13

answered Jul 27, 2019 at 19:43

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

P. Nick Over a year ago

I did debug my query with toSql(), but your function seem to give syntax error, unexpected '}'

Qirel Over a year ago

Forgot a colon at the end of orWhere(), that should be fixed now.

P. Nick Over a year ago

For some reason it's still not returning the correct rows. I'm still looking at about 40 rows instead of 3...

Qirel Over a year ago

Alright, let's take a step back here. Can you create a DB Fiddle (for example at sqlfiddle.com), with the data you have - and which rows you expect to get back? My guess is that the orWhere() part will trigger on 37 more rows than intended, but without seeing the actual data and which rows you expect, its hard to say.

Qirel Over a year ago

Alright, so how do you get the class ID of 14? We can also simplify the query quite a bit.

|

Collectives™ on Stack Overflow

Laravel 5.8 Eloquent doesn't work as intended despite the raw dumped query from it works

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related