Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Working with Combined Expressions in WHERE Clauses in Laravel 4

DZone's Guide to

Working with Combined Expressions in WHERE Clauses in Laravel 4

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

laravel-combined

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

SELECT * FROM users WHERE status=1 AND (fname = 'John' OR lname='Doe')

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.

SELECT * FROM users WHERE status=1 AND fname = 'John' OR lname='Doe'

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

SELECT * FROM users WHERE (status=1 AND fname = 'John')
 
OR
 
SELECT * FROM users WHERE lname='Doe'

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

static function searchByName($fname, $lname){
    return User::where("status", 1)
        ->where(function ($query) use ($fname, $lname) {
            $query->where("fname", '=', $fname)
                ->orwhere("lname", '=', $lname);
        })
        ->get(['*']);
}

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

static function searchByName($fname, $lname)
{
    return User::where("status", 1)
        ->where("fname", '=', $fname)
        ->orWhere("lname", '=', $lname)
        ->get(['*']);
}

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 :)


Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}