Over a million developers have joined DZone.

Working with Combined Expressions in WHERE Clauses in Laravel 4

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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


Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

Published at DZone with permission of Hasin Hayder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}