Zend Framework and the Zend_Db_Select::where() and ::orWhere() Problem

— Geekery, Programming

To say that I dislike Zend Framework, would be an understatement. While it is powerful and capable of quite a bit, I find it to be a bit overkill for many (if not most) tasks and bloated in so many ways. But, I’m not a huge fan of frameworks to begin with. As programmers, we find ourselves having to work with things we dislike all to often. Providing decent documentation and examples allows for this process to be a bit more enjoyable. Zend Framework documentation is, mostly helpful but it definitely has it’s issues.

When using the Zend_Db_Select (I’d rather be using PDO) object, I came across the necessity to execute a MySQL query with some OR conditions. This seems like a simple enough task especially considering the availability of an orWhere() function to use instead of a where() function. Reading the description of the functionality from the manual reveals the following:

If you need to combine terms together using OR, use the orWhere() method. This method is used in the same way as the where() method, except that the term specified is preceded by OR, instead of AND.

Changing AND to OR is the only thing this function provides. Searching around for gripes about the functions yields various complaints regarding the relative uselessness of the orWhere() for many cases.

Let’s say you want to find instances of a record that is active (active = 1) and where the date is set to null (0000-00-00) or in the future (> 2011-08-09). When using the where() and orWhere() functions, it feels somewhat natural to try something like this after reading the documentation:

$select = $db->select()
             ->from("some_table")
             ->where("active = ?", 1)
             ->orWhere("my_date > ?", "2011-08-09");
             ->orWhere("my_date = ?", "0000-00-00")

Unfortunately, this produces a query that looks like this:

SELECT * FROM some_table 
WHERE (active = 1) 
OR (my_date > '2011-08-09') 
OR (my_date = '0000-00-00')

This would return any records where active is 1 or my_date is in the future or my date is null. Not quite what we’re looking for. Progressing from this, one might consider switching the first orWhere() to a where() to add in the appropriate AND:

$select = $db->select()
             ->from("some_table")
             ->where("active = ?", 1)
             ->where("my_date > ?", "2011-08-09");
             ->orWhere("my_date = ?", "0000-00-00")

Trying that doesn’t get us there either:

SELECT * FROM some_table 
WHERE (active = 1) 
AND (my_date > '2011-08-09') 
OR (my_date = '0000-00-00')

Perhaps the documentation from Zend can shed some light on this problem. (A side note to the Zend documentation folks, who still struggle to make documentation that doesn’t suck: it isn’t that hard to create documentation where it’s possible to highlight only code without grabbing the line numbers. Please consider looking into this. Thanks.)

The manual lets us know that the Zend_Db_Select is completely useless for even a moderately useful OR query and tells us to do something like this:

$minimumPrice = 100;
$maximumPrice = 500;
$select = $db->select()
             ->from('products',
             ->where("price < $minimumPrice OR price > $maximumPrice")

This wouldn’t be so bad except for the fact that it doesn’t use query parameterizing and doesn’t take into account the fact there’s a pretty good chance we won’t be dealing with straight integers all the time. I find it to be a weak and lacking example. Trying to rouse someone from the Zend Framework team to understand and possibly address the insufficiencies provides a response just as useless:

The where() and orWhere() methods of Zend_Db_Select support a majority of common queries. This class is not able to implement a method interface to the entire SQL language.

Complex queries that are not supported by Zend_Db_Select must be written in SQL and submitted as a string to the Zend_Db_Adapter_*::query() method.

Translating this, from my perspective, sounds a bit like: “We figured those helper methods would be useful for most things but, since they aren’t, we’re going to blame the complexities of the SQL language and tell you to use the base query method to generate your own queries.”

Adding insult to injury, the codebase I currently work on further abstracts database functionality by providing a search() method to our model’s data adapters to grab things from the database using arbitrary bits of the model or, even other models. Querying simple things such as columns already available could be done by passing an array to the search() method containing a key name for the column and, either the value you wanted it to be equal to, or a different operator with a value associated with it. This would intelligently determine the operator and value to check against the column by providing a simple array like:

array (
    'active' => 1, 
    'my_date' => array (
            'operator' => '>', 
            'value' => '2011-08-09'
        )
    )
);
 
/**
   * Results in:
   *
   * $select->where('active = ?', 1);
   * $select->where('my_date > ?', '2011-08-09');
   */

In the example above, we’d find records where active was equal to 1 and startDate was not equal to ‘0000-00-00’. While this worked fine for simple searches, it failed to provide the accessibility to queries where an OR statement was needed.

Supplying a fix that follows a similar style and also takes into account the necessity of correct quotations around values (that weren’t INTs) didn’t prove too hard. Modified search parameter arrays could be provided like so:

$date = date('Y-m-d');
$searchArray = array (
    'active' => 1,    
    'my_date'   => array (
        'operator'  => 'OR',
        'value'     => array(
            array (
                'operator' => '>',
                'value'    => $date
            ),
            array (
                'operator' => '=',
                'value'    => '0000-00-00'
            )
        ),
    )
);

Within our search method, we would check to see if the operator was set to OR. If so, the code would go through a simple construction process to add the appropriate AND query with our parenthesis in the right place and it looked something like this:

if ($operator == 'OR') {
    if (is_array($val)) {
        $queryParts = array();
        foreach ($val as $orArray) {
            $orValue = $orArray['value'];
            $orOperator = $orArray['operator'];
            $queryParts[] = sprintf('%s %s %s', $column, $orOperator, $this->db->quote($orValue));
        }
        $select->where(implode(' OR ', $queryParts));
    }
}

Finally we were able to have our code generate the SQL we were looking for:

SELECT * FROM some_table 
WHERE (active = 1) 
AND (my_date > '2011-08-09' OR my_date = '0000-00-00');

While this doesn’t fix the broken and rather useless Zend_Db_Select::orWhere() issue, it is a useful workaround.

Hopefully others can read and investigate this methodology to find the path towards database adapter enlightenment in the fogginess that is Zend_Db_Select. Personally, I find I always have to keep my flashlight handy when trudging through the long, twisted and dark tunnels that are Zend Framework. Good luck, traveler!

Related Posts