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!

score 23 · Accepted Answer · edited Nov 02 '17 at 15:40

23

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.

edited Nov 02 '17 at 15:40

Naktibalda

13,705
5
35
51

answered Dec 07 '10 at 23:17

karim79

339,989
67
413
406

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. – jmbertucci Dec 08 '10 at 15:06
The [Zend_Db_Select](http://framework.zend.com/manual/1.12/en/zend.db.select.html) link didn't work for me here's an updated link to the docs. – Mark Steudel Oct 02 '15 at 18:21

score 5 · Answer 2 · answered Aug 31 '16 at 09:00

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.

Best and cleanest of all answers, helped me a lot. Zend should also have had this better supported — Phliplip, Dec 27 '18 at 13:24

score 0 · Answer 3 · answered May 16 '14 at 13:29

0

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);

answered May 16 '14 at 13:29

Vulman

21
2

score 0 · Answer 4 · answered Jul 12 '17 at 13:18

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"

Complex WHERE clause with Zend_Db using multiple AND OR operators

4 Answers4

Linked

Related