Zend\Db\Sql\Select - The Basics (Columns, Limit & Order)

Introduction

Welcome to the third and last part in this series, introducing you to working with the \Zend\Db\Sql\Select classes in Zend Framework 2. In part one we looked at building SQL Where clauses using the where related functions, predicates and closures, as well as compound queries.

In part 2, we looked at all forms of SQL joins as well as a slightly more esoteric feature of SQL - UNIONS. Here, in part 3, in the words of Coldplay, we’re going back to the start, and looking at the fundamentals.

This will include:

• The class constructor
• The limit and order functions
• The Expression class

Class Constructor

Let’s start by looking at the basics of the class, the constructor. Put simply, a Select class can be instantiated in one of two ways, these are as follows:

use Zend\Db\Sql\Select;
$select = new Select();

and

use Zend\Db\Sql\Select;
$select = new Select(<table name>);

The only difference is that in the second, we’re specifically binding the Select object to a specific table. The table name can be specified in one of three ways. These are:

• String - the name of the table
• TableIdentifier object
• Array - with one element, a string which is the name of the table

A string and array we don’t need to cover. Touching on TableIdentifier objects, just for a moment, you can see them as a utility object, which can easily and conveniently store the table name and schema information for us. They’re rather simple to instantiate, with a constructor signature as follows:

__construct($table, $schema = null)

Usage Warning:

What’s important to note here, is that if a table name is provided to the constructor, that a “read-only” mode is set on the class. What this means is that you won’t be able to add join conditions, as a Exception\InvalidArgumentException will be thrown with following error message:

'Since this object was created with a table and/or schema in the constructor, it is read only.'

Columns

Ok, we now have the very basic foundations of our Select object. Now, we need to consider which columns we’ll be (or not be) bringing back in our query result set. I say this, because by default, if we don’t specify which columns to return, the Select class constant SQL_STAR is used, which is short for:

SELECT *

Unless you’re a bit of a masochist, or not concerned about performance, littering your code with them is going to get very ugly, very fast. It’s fine now you may say, my data set is quite small.

That may be the case, but stop and ask yourself the following questions:

• Will you be there forever or be the only maintainer?
• Will you or your team always be aware of all datasource changes?
• Will there be a dedicated person checking the queries against the datasource?

I’d suggest it’s fair to say that it’s better to err on the side of caution in this regard. You can get in to the habit of using views over direct table queries. But I’ll leave that discussion for another day.

Back to restricting columns. You should always limit the the dataset returned in your queries to only the columns that you need, and nothing more. If your needs change, you can always change your queries.

This is better than incurring the overhead for retrieving information that you’ll never use. Especially given we’re environmentally-friendly developers - right?

So to do this, we use the columns function, which has the following signature:

public function columns(array $columns, $prefixColumnsWithTable = true)

The $columns array affords us some flexibility. We can:

• Pass in a simply scalar array, listing only the columns we want
• Pass in an associative array, listing the columns we want, with aliases
• Pass in a more complex associative array, listing the columns we want, with aliases, but this time, instead of a string for the column, we pass in an Expression object

Let’s look at the first two, then focus on expression objects in a bit more depth next. The first example shows a scalar array of columns from tblusers, which we’ve been using throughout this series.

use Zend\Db\Sql\Select;

$select = new Select('tblusers');
$select->columns(array(
    'UserID', 'Username', 'FirstName', 'LastName'
));

Now that’s fine, but let’s say that we have a legacy application (we’re not using views) and we need FirstName to be first_name and LastName to be last_name. Let’s pass in an associative array to show how we’d alias the columns.

$select = new Select('tblusers');
$select->columns(array(
    'UserID', 'Username',
    'first_name' => 'FirstName', 'last_name' => 'LastName'
));

As you can see, nothing much too it. It’s exactly like the columns example for joins in part two.

Expression Objects

Expression classes are one that you’re not going to be able to avoid forever. The reason being, unless you’re writing plain vanilla SQL, or everything is bound up in Stored Procedures, you’ll need to use the functions of you native database at some point.

And why not, when they offer so much functionality that we would otherwise need to (and likely shouldn’t) (re)implement in PHP code. Think about the normal set of operations that you’re likely to perform on data, such as strings.

The catch though, is that you can’t just pass in an expression as a string, such as the following:

SELECT CONCAT(FirstName, ", ", LastName) AS FullnameFirstLast

If we were to attempt this, the expression would be prepended with the table name, resulting in a failed query when it reached the database level. What we need to do is pass in an Expression object, which gets around this issue. Take the following example, where we want to get the count of records:

$select = new Select('tblusers');
$select->columns(array(
    "recordCount" => new \Zend\Db\Sql\Expression("COUNT(*)")
));

Here, we’ve specified the expression alias on the left, recordCount, and the expression object on the right. This will result in the following SQL:

SELECT COUNT(*) AS recordCount
FROM tblusers;

To be honest, it doesn’t get more complex than this. But let’s look at a more complex example. Let’s look at a parameterised example.

$select = new Select('tblusers');
$select->columns(array(
    "FullName" => new \Zend\Db\Sql\Expression(
        "CONCAT(?, ' ', ?)", array("FirstName", "LastName")
    )
));

Resulting in the following SQL:

SELECT CONCAT('FirstName', ' ', 'LastName') AS "FullName" FROM "tblusers"

In this example, we’ve made the expression a bit more flexible. Whereas we could have specified the columns directly in the CONCAT expression, here we’ve allowed them to be changed around.

This would help where the display may need to change, depending on circumstance. Now, you only need one function, not two. I’ll skip going in to depth on the Expression object at this stage. But I’ll revisit it further in a later tutorial.

Limit

Not all database natively support the LIMIT clause. It is supported by MySQL, Sybase, PostgreSQL and SQLite, amongst others. However Oracle, Ingres, Informix and MSSQLServer don’t. However, through the auspices of the \Zend\Db limit function, we’re able to mimic the feature, if it doesn’t exist natively in your database of choice.

Taking our previous example, let’s limit the number of records to the first 100.

$select = new Select('tblusers');
$select->columns(array(
    "FullName" => new \Zend\Db\Sql\Expression(
        "CONCAT(?, ' ', ?)", array("FirstName", "LastName")
    )
))->limit(100);

Not really much to it. This will produce the following SQL

SELECT CONCAT('FirstName', ' ', 'LastName') AS "FullName"
FROM "tblusers"
LIMIT 100;

However, let’s say that we want the second 200. To do that, we include the offset function as well, as in the following:

$select = new Select('tblusers');
$select->columns(array(
    "FullName" => new \Zend\Db\Sql\Expression(
        "CONCAT(?, ' ', ?)", array("FirstName", "LastName")
    )
))->limit(100)->offset(100);

Giving us the following SQL:

SELECT CONCAT('FirstName', ' ', 'LastName') AS "FullName"
FROM "tblusers"
LIMIT 100 OFFSET 100;

Order

Ordering is likely one of the first operations that you’ll seek to perform, after you’ve addressed the needs of which columns to include and from which tables you need the information from.

To keep it simple \Zend\Db\Sql\Select offers the order function. This function takes one argument, in the form of a string or array of strings. There’s nothing special here, you simply pass in a list of order by expressions that you want to set in your SQL query.

Let’s update our last example, adding in first and last names and then ordering the results by last name in descending order. In code, it would be as follows:

use Zend\Db\Sql\Select;

$select = new Select('tblusers');
$select->columns(array(
    "FullName" => new \Zend\Db\Sql\Expression(
        "CONCAT(?, ' ', ?)", array("FirstName", "LastName")
    ), "FirstName", "LastName"
))
->order("LastName DESC")
->limit(100)
->offset(100);

Giving us the following SQL:

SELECT CONCAT('FirstName', ' ', 'LastName') AS "FullName",
    "FirstName", "LastName"
FROM "tblusers"
ORDER BY LastName DESC
LIMIT 100 OFFSET 100;

Let’s say that we are going to have many users with the same last name and we want to order them secondarily by first name. Let’s update our example an do this:

$select = new Select('tblusers');
$select->columns(array(
    "FullName" => new \Zend\Db\Sql\Expression(
        "CONCAT(?, ' ', ?)", array("FirstName", "LastName")
    ), "FirstName", "LastName"
))
->order("LastName DESC, FirstName DESC")
->limit(100)
->offset(100);

Giving us the following SQL:

SELECT CONCAT('FirstName', ' ', 'LastName') AS "FullName",
    "FirstName", "LastName"
FROM "tblusers"
ORDER BY LastName DESC, FirstName ASC
LIMIT 100 OFFSET 100;

Conclusion

So there we have it - a good overall introduction to \Zend\Db\Sql\Select in three parts. Let’s quickly recap:

In part 1, we focused specifically on building all forms of Where clauses, from the simple, to nested, showing how we can build them with strings, arrays and Predicate objects.

In part 2, we looked at how to perform all forms of joins, from a simple inner join to left, right and outer joins, even a self-join. We finished with looking at how to create a UNION query, even though there’s not a specific function available for doing so.

Here in part 3, we’ve gone back to the start, back to basics and covered the class constructor, columns, column aliases and column expressions, finishing up with limiting and ordering records.

I hope that this series has given you a solid foundation for understanding and using \Zend\Db\Sql\Select. I encourage you to give these examples a go and explore the individual classes. There’s such a wealth contained inside.

So, was anything missed, not covered in depth, covered too deeply? Say so in the comments or tweet me. What do you want to see more of?

Till next time, happy coding.