Laravel WHERE Query To Match Multiple Columns - Fixed

INTRODUCTION
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.
ABOUT THE AUTHOR
Kaustubh has been coding with Laravel framework for several years and is passionate about helping newbie Laravel developers get started with Laravel - world's most popular PHP Framework.
Kaustubh Katdare
Kaustubh Katdare
CEO @ CrazyEngineers, TEDx Speaker, Guitarist

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.


DG71-laravel-eloquent-where-query-sql.jpg

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);
})->get();
//------------------ 

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. 

Share this content on your social channels -