When retrieving records from tables in our database, you often have to write combined expressions in WHERE clauses like

[sourcecode language=”sql”]
SELECT * FROM users WHERE status=1 AND (fname = ‘John’ OR lname=’Doe’)
[/sourcecode]

The query above is pulling out all users who’s status is set to 1, and fname is equal to ‘John’ or status is 1 and lname is equal to ‘Doe’. Now this query is significantly different from the following one.

[sourcecode language=”sql”]
SELECT * FROM users WHERE status=1 AND fname = ‘John’ OR lname=’Doe’
[/sourcecode]

Can you tell us what can go wrong with a statement like this? Well to find out more, let’s have a look at how this query will be parsed by database engine

[sourcecode language=”sql”]
SELECT * FROM users WHERE (status=1 AND fname = ‘John’)

OR

SELECT * FROM users WHERE lname=’Doe’
[/sourcecode]

So this query will pull every record that has a status set to 1 and fname equivalent to ‘John’. Then, with the previously pulled records, it will also pull all the users who has a lname equal to ‘Doe’, but this time it will pull out all users disregarding their status. Which means that users with status = 0 or 1 or 2 will also be in the resultset. I bet that is not what you were looking for while you’re writing this query and clearly understands the difference between these two queries. So you got the point of grouping or combining logical expressions and the impact that can have in your resultset. Writing a combined logical expression is easy in SQL. But what if you’re told to do the same operation using Laravel 4’s built in ORM which is called Eloquent ORM. It’s a little tricky to do the same operation in eloquent because of this combined expression, and it needs an anonymous function 🙂 Let’s have a look at the following code in our User model

[sourcecode language=”php”]
static function searchByName($fname, $lname){
return User::where("status", 1)
->where(function ($query) use ($fname, $lname) {
$query->where("fname", ‘=’, $fname)
->orwhere("lname", ‘=’, $lname);
})
->get([‘*’]);
}
[/sourcecode]

Now searchByName(…) function will work as we expected. You can try the following one and see the difference and notice how it is VERY MUCH different from our expectation

[sourcecode language=”php”]
static function searchByName($fname, $lname)
{
return User::where("status", 1)
->where("fname", ‘=’, $fname)
->orWhere("lname", ‘=’, $lname)
->get([‘*’]);
}
[/sourcecode]

Eloquent ORM’s where function can make use of callbacks in this way. Please note how we used anonymous function as callback that also uses $fname and $lname from the arguments. That’s mainly it. I hope that you’ve enjoyed this article 🙂