Issues

ZF-1726: Make the "where" clause parameters more consistent

Description

Old summary: Normalize the "where" clause parameters that are accepted by Zend_Db_Table's fetchAll/fetchRow and update/delete methods.

I noticed that the where parameter for update and delete methods for Zend_Db_Table work a little differently than those for the fetchAll and fetchRow methods. Looking at the code, this seems to be based on the fact that update/delete proxy their corresponding methods in Zend_Db whereas fetchAll/fetchRow use Zend_Db_Select to build their queries. As it is now, all four of the methods accept the where clause either as a string or as an indexed array of strings (though this method is undocumented for fetchAll/fetchRow). The strings from the array are ANDed together without being quoted. e.g.


$where = $table->getAdapter()->quoteInto('bug_id = ?', 1234);

or


$where[] = "reported_by = 'goofy'";
$where[] = "bug_status = 'OPEN'";

However fetchAll/fetchRow also accept another undocumented format for the "where" parameter which automatically does the quoting for you. It takes an associative array where the keys are the condition with a placeholder and the values are the values to be replaced. Values are automatically quoted and terms are joined together using ANDs. e.g.


$where = array('bug_status = ?' => 'NEW', 'reported_by = ?' => 'talawahdotnet');
$rows = $table->fetchAll($where);

It would be nice if theses interfaces where unified and documented so that they accepted all three formats.

Comments

Updated summary, formatted code in description, assigned to Bill Karwin.

By modfying the _whereExpr function on Zend_Db_Adapter_Abstract the where clause for the Zend_Db_Table_Abstract::update/delete functions is structured in the same way as the where clause for the fetchRow/fetchAll fucntions.

$where can be:

1) An instance of a Zend_Db_Expr 2)A string 3)An array of strings (each one a where condition) 4)An array of where clauses where the array key is a where clause of the form " ?" and the value will be quoted into the key to replace the question mark. Eg: array('id = ?' => 123, 'created_date > ?' => '2007-08-27 09:34:08' )

The new function would be:

    
     /**
     * Convert an array, string, or Zend_Db_Expr object
     * into a string to put in a WHERE clause.
     *
     * @param mixed $where
     * @return string
     */
    protected function _whereExpr($where)
    {
        if (empty($where)) {
            return $where;
        }
        if (!is_array($where)) {
            $where = array($where);
        }
        foreach ($where as $cond => &$term) {
            // is $cond an int? (i.e. Not a condition)
            if (is_int($cond)) {
                // $term is the full condition
                if ($term instanceof Zend_Db_Expr) {
                    $term = $term->__toString();
                }
            } else {
                // $cond is the condition with placeholder,
                // and $term is quoted into the condition
                $term = $this->quoteInto($cond, $term);
            }
            $term = '(' . $term . ')';
        }

        $where = implode(' AND ', $where);
        return $where;
    }

Patch modifies Zend_Db_Adapter_Abstract such that all where clauses use consistent arguments.

Resolved with attached patch

Reopening because it appears that the patch has not been applied to SVN; reassigning to [~bkarwin].

Please categorize/fix as needed.

Past due date

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Ralph, can you please look in to this and get some closure when you have a little time?

Will evaluate in 2 weeks

Fixed with the patch provided at r14358

Wil, I reopen it because it fails with Zend_Db_Adapter_Oracle and testAdapterUpdateWhereArrayWithVariable (only this adapter extends the update method). I will correct it in the next days.

It also fail for the 2 asssociated tests with Db2, Oracle and Pdo_Oci but just for a mistake in tests.

Fixed with SVN14365 & SVN14366