Complex WHERE clause with Zend_Db using multiple AND OR operators

Question

I want to generate this complex WHERE clause in Zend_Db:

SELECT * 
FROM 'products' 
WHERE 
    status = 'active' 
    AND 
    (
        attribute = 'one' 
        OR 
        attribute = 'two' 
        OR 
        [...]
    )
;

I've tried this:

$select->from('product');
$select->where('status = ?', $status);
$select->where('attribute = ?', $a1);
$select->orWhere('attribute = ?', $a2);

and that produced:

SELECT `product`.* 
FROM `product` 
WHERE 
    (status = 'active') 
    AND 
    (attribute = 'one') 
    OR 
    (attribute = 'two')
;

I did figure out one method of making this work but I felt it was sort of 'cheating' by using PHP to combine the "OR" clauses first and then combine them using Zend_Db where() clause. PHP code:

$WHERE = array();
foreach($attributes as $a):
    #WHERE[] = "attribute = '" . $a . "'";
endforeach;
$WHERE = implode(' OR ', $WHERE);

$select->from('product');
$select->where('status = ?', $status);
$select->where($WHERE);

That produced what I was looking for. But I'm curious if there's an "official" way of getting that complex WHERE statement (which really isn't too complex, just adding some parenthesis) with using the Zend_Db tool, instead of combining it in PHP first.

Cheers!

Sabadilla

Answer 1

This would be the 'official' way to get you the parentheses as specified (see Example #20 in the Zend_Db_Select documentation):

$a1 = 'one';
$a2 = 'two';
$select->from('product');
$select->where('status = ?', $status);
$select->where("attribute = $a1 OR attribute = $a2");

So, what you are doing does seem reasonable, given that you do not know how many attributes you have ahead of time.

Prosperity

Yes, your solution, from the ZF reference guide, is basically how I came up with my final example. Perhaps that is the correct way to do it then. It just feels counter-intuitive given Zend_Db_Select (ZDS) is suppose to generate your SQL for you, but we're having to generate a portion of it outside ZDS. Perhaps, this is something that could use an extension so we can keep all the code in Zend_Db_Select. – Sabadilla

The Zend_Db_Select link didn't work for me here's an updated link to the docs. – Exudate

Answer 2

If using the chosen answer you would need to remember to quote the values before constructing the query to guard against SQL injection.

Another option that uses Zend Db Select to create the query and also quotes the values would be to do it in 2 stages:

/// we just use this select to create the "or wheres"
$select1->from('product');
foreach($attributes as $key => $a) {
    if ($key == 0) {
    /// we don't want "OR" for first one
        $select1->where("attribute = ?", $a);
    } else {
        $select1->orWhere("attribute = ?", $a);
    }   
}

/// now we construct main query
$select2->from('product');
$select2->where('status = ?', $status);
$select2->where(implode(' ', $select1->getPart('where')));

This way Zend Db Select does all the SQL generatation. An old question but hopefully this idea might be of use to someone with a similar problem.

Pedalfer

Best and cleanest of all answers, helped me a lot. Zend should also have had this better supported – Distressful

Answer 3

I use this solution and it seems work as wanted.

$select->from('product');
$select->where('status = ?', $status);
$select->where('(attribute = ?', $a1);
$select->orWhere('attribute = ?)', $a2);

Preciado

Answer 4

If your attribute is the same table column and you want to check it for equal to one of several values, than you can use IN :

$select->from('product');
$select->where('status = ?', $status);
$select->where('attribute IN (?)', [$a1, $a2, ...]);

or

$select->where('attribute IN (:someName)');
$select->setParameter('someName', [$a1, $a2, ...]);

Otherwise I see no alternative to decision above with "attribute = $a1 OR attribute = $a2"

Handoff