CrazyEngineers V4: Early Access

We are developing the next version of CrazyEngineers. If you wish to receive latest updates and early access, click the link below.

  1. Home >
  2. Apps >
  3. Tutorials >

Laravel WHERE Query To Match Multiple Columns - Fixed

Where queries in Laravel can be super confusing when you are trying to match multiple columns in your database. Let's fix this problem for once.
Kaustubh Katdare
Rank A1 - PRO

Laravel's Eloquent is quite awesome when it comes to dealing with databases. However, without understanding what Eloquent is actually doing behind the scenes, it could be super frustrating when you do not get desired results from database.

I was dealing with a simple problem but ended up wasting about 3 hours because I didn't read the documentation well. Let me explain. 

The Problem:

First look at the sample entry in the database table -

id | user_a | user_b | status | task
1  | 10     |   20   |  1     | some-task
2  | 5      |   10   |  0     | another-task

Assuming that my user_id is 10, I wished to fetch the record that had me in either column user_a OR user_b and had a status of 1. The query I wrote was as follows -

$task = MyTasks::where('status', 1)->where('user_a', 10)->orWhere('user_b', 10)->get();

My assumption was that Eloquent would look first find out the columns where status = 1, and then look in both columns - user_a OR user_b and return the record #1. However, it did not work as I expected.


The Solution [Correct WHERE Query]:

The root of the problem was my understanding of the SQL Query. Eloquent generated following query as expected -

select * from `tasks_table` where `status` = ? and `user_a` = ? or `user_b` = ?

The problem with it is that it's looking at just one column! In order to fix it, we need following query -

select * from `tasks_table` where `status` = ? and (`user_a` = ? or `user_b` = ?)

That bracket wrapping the columns makes all the difference. So how do you write that query using Eloquent? Simple.

$tasks = DB::table('tasks_table')->where('status,'=',1)
                ->where(function($query) {
                    $query->where('user_a', '=', 10)
                        ->orWhere('user_b', '=', 10);

With that simple change in the query, I got the desired results. 

I hope this helps helps you figure out how Eloquent and SQL works in in Laravel. If you have any questions, let me know below. 

#PHP #Laravel #Database #Eloquent #SQL #MySQL