Building and Executing SQL Queries In Zend

Whilst there are many ways for building and executing SQL queries in Zend Framework 2, the two that I usually use, and which are also used in the ZF2 manual, are closures and the selectWith function. I previously wrote a three part series, showing how to get started using the \Zend\Db\Sql classes with Zend Framework 2, but I didn’t cover how to actually run them. So in today’s tutorial, let’s do that.

Closures

Starting with closures, have a look at this first example:

$results = $this->tableGateway->select(function (Select $select)
    use($searchCriteria, $sortBy, $sortDir = 'ASC')
{

Here, we’re calling the select function, on a tableGateway object initialised in the class constructor, passing in a new Select object, along with three arguments:

• $searchCriteria: an InputFilter object with data from a submitted POST request
• $sortBy: The column to sort the results by
• $sortDir: The direction to sort, by default ascending

So far, quite simple.

NB: If this is your first time running queries in Zend Framework 2, don’t worry, I’ll show how to setup a tableGateway object at the end of the tutorial.

$select->where->like(
    'Category',
    $inputFilter->getValue("searchCriteria") . "%"
)
->or->like(
    'Category',
    "%" . $inputFilter->getValue("searchCriteria")
);

Here, we’re adding a like clause on the SQL query, to check if the table column, Category, either starts with, or ends with the value of searchCriteria which we retrieved from the InputFilter variable, $inputFilter.

NB: I commonly use InputFilter objects in connection with Form objects to aid in filtering and sanitising information retrieved from the submitted POST data.

if (!empty($sortDir) && !empty($sortBy)) {
    $select->order(
        sprintf(
            "%s %s",
            $sortBy,
            $sortDir
    ));
}

Here, if we both $sortDir and $sortBy were specified, we add an order clause to the Select object with them.

if (!empty($sortBy)) {
    $select->order(sprintf("%s ASC", $sortBy));
}
});

If $sortBy wasn’t specified, then we default to ascending order, for the search results. Strictly this shouldn’t be necessary, as $sortBy is an optional parameter. But this helps avoid an error if it’s null.

The closure will initialise $results as a Zend\Db\Sql\ResultSet object, which is traversable or iterable. This would allow us to also pass $results to a new Zend\Paginator object, so we can render the results over a series of pages, iterate it as one large list and so on. Very flexible.

The generated SQL query would look something like this:

SELECT *
FROM
WHERE Category LIKE '?%' OR Category LIKE '%?'
ORDER BY Category ASC

Using selectWith

Now closures are really, really, handy, but they’re not the only way. What’s more, I don’t find them that easy to test. So I increasingly prefer to use selectWith instead. Here’s an example of how to use it:

$category = $inputFilter->getValue("searchCriteria");
$select = $this->tableGateway->getSql()->select();
$select->where(array("Category" => $category));
$resultSet = $this->tableGateway->selectWith($select);

Instead of implicitly passing a Select object to the closure, we’ve retrieved a \Zend\Db\Sql object, by calling getSql() on our tableGateway variable. We then retrieved a Select object, by calling select() on the retrieved Sql object.

This was efficiently combined into one step, by using the available fluent interface, implemented in the Zend\Db\Sql classes.

Once the Select object was retrieved, it was really easy to start calling its methods to build it up programmatically. We could use the order function as well as the others, but I’ve kept this example deliberately simple.

With the where function called, the selectWith function is called on tableGateway, passing in $select. As with the closure, the return value of selectWith is a \Zend\Db\ResultSet object, containing the results, if any which matched the executed SQL query.

The generated SQL query would look something like this:

SELECT *
FROM
WHERE Category = ?

The Table Object

As I mentioned earlier, if this is your first time building and running SQL queries with the Zend SQL classes, how I retrieved the TableGateway object in my model class may have been a bit confusing. So here’s how.

public function getServiceConfig()
{
    return array(
    'factories' => array(
        'CategoryTableGateway' => function ($sm) {
            $dbAdapter = $sm->get(
                'Zend\Db\Adapter\Adapter'
            );
            $resultSetPrototype = new ResultSet();
            $resultSetPrototype->setArrayObjectPrototype(
                new Category()
            );
            return new TableGateway(
                'tblcategory',
                $dbAdapter,
                null,
                $resultSetPrototype
            );
        }
        'CategoryTable' =>  function($sm) {
            $tableGateway = $sm->get(
                'CategoryTableGateway'
            );
            $table = new CategoryTable($tableGateway);
            return $table;
        },
    )
)
)

In the getServiceConfig function of my module’s Module.php class, I would have a configuration, similar to the above. This does two things, firstly, it provides a TableGateway object, which binds to the tblcategory table in my database, implementing the TableGateway pattern.

This makes it simple for the class to interact with the table’s data. I’ve then configured another service which returns a model class, initialising it with the TableGateway service. Now, I can use the TableGateway connection to run queries on the table as I see fit.

There’s not quite time to go over the other specifics in the configuration above. So to get more information about that, check out the excellent post by Evan Courey.

Wrapping Up

Firstly, sorry for not including this in in the original series. But for those that weren’t familiar with how to run the query, here’s the solution.

As you can see, it’s really quite trivial to both construct and execute queries with Zend\Db. By using the related methods on a Select object, you can build nearly any query that you want, then either with a closure or selectWith, retrieve any matching results, which can be iterated over.

What’s your approach? Do you use one or both of these? Do you do something different?