\Zend\Db\Sql - Build SQL Where Clauses Easily and Efficiently

Introduction

In this week’s tutorial, we’re working through the basics of \Zend\Db\Sql\Where, showing how to build SQL Where clause predicates for your database queries. This will show how to, programmatically, build them in a very straight-forward and maintainable fashion.

We’ll show how to create an assortment of different predicates which you can use in everyday applications, from the simple, through to nested queries, making use of and, or, like, between and in conditions.

Even if you’re just getting started with Zend Framework 2, I’m sure that you’ll get something out of it, reducing the time you spend building queries.

If you’ve not used the \Zend\Db\Sql classes before, please familiarize yourself with it first. Otherwise, let’s dive right in.

Level: Intermediate

Time: 30 minutes

The Table Schema

For the purposes of this tutorial, the following MySQL-based table schema, tblusers, will be used:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| UserID              | int(11)      | NO   | PRI | NULL    | auto_increment |
| Username            | varchar(100) | YES  |     | NULL    |                |
| FirstName           | varchar(100) | YES  |     | NULL    |                |
| LastName            | varchar(100) | YES  |     | NULL    |                |
| EmailAddress        | varchar(100) | YES  |     | NULL    |                |
| FullName            | varchar(100) | YES  |     | NULL    |                |
| Password            | varchar(50)  | YES  |     | NULL    |                |
| Address             | text         | YES  |     | NULL    |                |
| City                | varchar(100) | YES  |     | NULL    |                |
| StateID             | int(11)      | YES  |     | NULL    |                |
| Zip                 | varchar(10)  | YES  |     | NULL    |                |
| Phone               | varchar(50)  | YES  |     | NULL    |                |
| Fax                 | varchar(50)  | YES  |     | NULL    |                |
| Email               | varchar(100) | YES  |     | NULL    |                |
| WebSite             | varchar(200) | YES  |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

It’s a simple, rather stock-standard, user table, containing the basic columns that you could reasonably expect to find.

The \Zend\Db\Sql Class

The \Zend\Db\Sql set of classes is defined, in the manual, as:

a SQL abstraction layer for building platform specific SQL queries via a object-oriented API. The end result of an Zend\Db\Sql object will be to either produce a Statement and Parameter container that represents the target query, or a full string that can be directly executed against the database platform. To achieve this, Zend\Db\Sql objects require a Zend\Db\Adapter\Adapter object in order to produce the desired results.

We’ll be focusing just on the where section of Zend\Db\Sql\Select today.

A Basic Where Clause

Ok, let’s start with a basic Where clause. Nothing fancy. Let’s find all records where the email address is “matthew@maltblue.com”. Have a look at the following code and then we’ll step through it.

$select = new Zend\Db\Sql\Select("tblusers");
$select->where(array('EmailAddress' => "matthew@maltblue.com"));

What we’ve done here is to initialize a new Select object with the base table to query. Then, called the where function on it, passing in an associative array. The key is the column name and the value is the value we want to match against the column. To see the SQL query generated from this call, we run the following:

$select->getSqlString();

This returns the following SQL query:

SELECT "tblusers".* FROM "tblusers" WHERE "EmailAddress" = 'matthew@maltblue.com'

This approach has a few good benefits, including more secure queries through internally constructing parameterized versions and helping to avoid SQL Injection attacks. It has the other benefit of being very easy to manipulate later.

What if we wanted to have several further conditions, such as first and last name? To do that, we just pass in further elements to the array, as follows:

$select->where(array(

    'EmailAddress' => "matthew@maltblue.com",

    'FirstName' => "matthew",

    'LastName' => "setter"

));

This would result in the following query (formatted for readability):

SELECT "tblusers".*
FROM "tblusers"
WHERE "EmailAddress" = 'matthew@maltblue.com'
    AND "FirstName" = 'matthew'
    AND "LastName" = 'setter'

Or Conditions

You’ll have seen that the query predicates we’ve generated so far have all been ANDed. What about OR? Let’s say that in addition to the previous query, we want to look for username’s which start with “anders”. Let’s look at how we’d do that:

$select->where(array(
    'EmailAddress' => "matthew@maltblue.com",
    'FirstName' => "matthew",
    'LastName' => "setter"
))
->where->or->like('Username',  "anders%");

Here, using the available fluent interface, we’ve added a like expression for checking the username but preceded it with or. This will create the following query:

SELECT "tblusers".*
FROM "tblusers"
WHERE "EmailAddress" = 'matthew@maltblue.com'
    AND "FirstName" = 'matthew'
    AND "LastName" = 'setter'
    OR "Username" LIKE 'anders%'

Like Conditions

Let’s look at some of the other conditions, commonly encountered with SQL queries. Firstly, the humble like. Below are two examples, the first using a predicate, the second using the fluent interface.

You can see here, that we’ve passed in an array to the where function, as we have been doing. In that, we’ve passed in a new Like predicate class, first specifying the column on which we’ll be performing the comparison and secondly, the like expression.

$select->where(
    new \Zend\Db\Sql\Predicate\Like('FirstName', "anders" . "%")
);

NB: If you’re not familiar with predicates, they allow for the specific construction of the various clause options available in a Where clause. There’s one for each type available.

Now, let’s look at it again through the fluent interface.

$select->where->like('FirstName', "anders" . "%");

I don’t, personally, suggest that you always use one or the other. Whatever best suits the needs of your applications, the ones that gain most traction and uptake from your development team will likely guide your choice of approach.

In Conditions

How about an IN or Between condition? Let’s say that we’re in Northern America and had a States table. In that states table, each state had a unique id in the range of 1 - 52. Let’s go further and say that StateID in our users table is a foreign key to it and we want to find only states who’s ids are in the range 1 - 12.

With this fictitious example, let’s construct an IN query. It would look like the following:

$select->where->in("StateID", range(1, 12));

If you’re not familiar with the range function, it generates an array with the minimum and maximum bounds specified. The generated query would be as follows:

SELECT "tblusers".*
FROM "tblusers"
WHERE "StateID" IN (
    '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'
)

Between Conditions

What about a Between query? it would be just about the same thing. Let’s look at how it would be constructed:

$select->where->between("StateID", 1, 12);

The generated SQL query would be:

SELECT "tblusers".*
FROM "tblusers"
WHERE "StateID" BETWEEN '1' AND '12'

Using Predicate Classes

So far, we’ve mostly passed in arrays and used some of the available functions. But what if you want to build a condition that you can re-use over and over again, across multiple queries in a class? In that case, there is the \Zend\Db\Sql\Predicate range of classes.

Let’s look at how we would remake our existing SQL query using Predicate classes.

$select->where(array(
    new \Zend\Db\Sql\Predicate\Like('FirstName', "Matthew%"),
    new \Zend\Db\Sql\Predicate\Like('FirstName',  "Peter%"),
    new \Zend\Db\Sql\Predicate\Like('LastName', "Smith%"),
    new \Zend\Db\Sql\Predicate\Like('LastName',  "Jackson%")
));

Using a Closure

Now for the third approach we can take: closures. This approach I’ve not explored in too much depth yet. But let’s walk build the following example using closures to see how they work.

$spec = function (\Zend\Db\Sql\Where $where) {
    $where->like('FirstName', 'Matthew%')
        ->and->like('FirstName', 'Peter%')
        ->and->like('LastName', 'Smith%')
        ->and->like('LastName', 'Jackson%');
};

$select->where($spec);

lessThan and greaterThan

Let’s say that we wanted to approach the State criteria differently. Let’s say that we wanted it to be either less than 40 and greater than 10. Let’s use the lessThan and greaterThan functions to generate that query.

$select->where

       ->lessThan("StateID", 40)

       ->and

       ->greaterThan("StateID", 10)

And here’s what it would look like:

SELECT "tblusers".* FROM "tblusers" WHERE "StateID" < '40' AND "StateID" > '10'

isNull and isNotNull

Ok, the last of the Predicates that we’ll be covering in today’s tutorial are isNull and isNotNull. Let’s say we want to return all the users that have a valid Zip Code. The schema allows for null, so these two predicates come in handy for performing a proper comparison on this column. Let’s look at both in action, using both the fluent and Predicate approaches.

$select->where->isNotNull("Zip")

$select->where(array(
    new \Zend\Db\Sql\Predicate\IsNotNull("Zip")
));

Nested Conditions

Now, all of these queries are fine. But they only really scratch the surface of what is possible and what you’re likely to need. What about more complex queries? What about nested queries?

Recently I was in just such a position; constructing a query with a series of conditions where I needed a component to be nested, otherwise the results would be flawed. Gladly, it’s pretty straightforward using the nest keyword.

Let’s say that we want to construct the following query:

SELECT *
FROM "tbluser"
WHERE StateID BETWEEN '1' AND '12'
OR (FirstName = "Matthew" OR FirstName = "Peter")
OR (LastName IN ("Smith", "Jackson", "Walpole") OR City = "Phoenix");

Ok yes, it’s a bit of a contrived example. I’ve not thought through fully the logic of it. But we’re focused on how to make it. So let’s look at how we could:

$select->where->between("StateID", 1, 12)
       ->where->or->nest
              ->equalTo("FirstName", "Matthew")
              ->or->equalTo("FirstName", "Matthew")
       ->unnest()
       ->where->or->nest
              ->in("LastName", array("Matthew", "Peter"))
              ->or->equalTo("City", "Phoenix");

Now, it’s not the nicest to read, but hopefully you follow how it works. Firstly, we’ve created the “WHERE StateID BETWEEN &‘1’ AND ‘12′” condition. Then, with “->where->or->nest”, we’ve set up the nested AND condition.

We passed in the associative array so that we match FirstName on Matthew or Peter. We then use “or->nest->where” to setup the next nested IN condition and finish up with the final OR.

I appreciate that on the first few reads through, it may be a little bit to get used to. But trust me that it gets really simple, really quick. In no time you’ll be building queries with little effort.

Deep Nested Conditions

One thing I want to draw to your attention is the unnest function call. It’s really important here, because without it, the second nested condition would be nested within the first, as follows:

OR ("FirstName" = 'Matthew' OR "FirstName" = 'Matthew' OR
    ("LastName" IN ('Matthew', 'Peter') OR "City" = 'Phoenix')
)

So be careful to build your queries as you intend them.

Parting Advice

Don’t rush it. Take your time and build up one piece at a time. If you jump in and try and do everything, you’ll likely end up really frustrated. Just learn one part, then the next and the next until you’ve mastered them all.

Now we haven’t covered the Where section of Select extensively in this tutorial; instead focusing on a range of options, using a range of approaches so that you get an understanding of how to use them in a variety of ways.

A Word of Thanks

I want to say a special thank you to a number of people in this post, including @samsonasik, +Jerome Hughes and +Olavo Neto. I was a little stuck on the nesting aspect and these three, wonderful, people took time out to guide me in the right direction when I was stuck and asked. Thank you folks. You have been invaluable to me. If you need a hand, get in touch with them.

Conclusion

So, there you have it. We’ve worked through how to create simple to more complex, nested, Where predicates for database SQL queries through using the \Zend\Db\Sql\Where class in Zend Framework 2. I hope that you’ve seen just how expressive and clear writing Where predicates with \Zend\Db\Sql\Where is. There are more powerful database layers, such as Doctrine. But \Zend\Db’s really come a long way since ZF1.

Are you using \Zend\Db\Sql to build your queries? Does it make your life easier and your code more maintainable? Tweet me your feedback.

Sneak Peak at Next Week

In part two of this series, we’ll be building on this week’s as we continue to explore the ins and outs of \Zend\Db\Sql\Select.