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

What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

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


What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}