jmbertucci jmbertucci - 3 months ago 21
SQL Question

Complex WHERE clause with Zend_Db using multiple AND OR operators

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!

Answer

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.

Comments