Zend Framework Select Operator Precedence

Question

Hi everyone,

I am trying to use Zend_Db_Select to write a select query that looks somewhat like this:

SELECT * FROM bar WHERE a = 1 AND (b = 2 OR b = 3)

However, when using a combination of where() and orWhere(), it seems impossible to use condition grouping like the above.

Are there any native ways in Zend Framework to achieve the above (without writing the actual query?)

Answer 1

From the manual (Example 11.61. Example of parenthesizing Boolean expressions)

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price < 100.00 OR price > 500.00)
//     AND (product_name = 'Apple')

$minimumPrice = 100;
$maximumPrice = 500;
$prod = 'Apple';

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where("price < $minimumPrice OR price > $maximumPrice")
             ->where('product_name = ?', $prod);

Answer 2

The above reference is great, but what if you are playing with strings?

Here would is the above example with strings...

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (product_name = 'Bananas' OR product_name = 'Apples')
//     AND (price = 100)

$name1 = 'Bananas';

$name2 = 'Apples';

$price = 100;

$select = $db->select()

->from('products',
                    array('product_id', 'product_name', 'price'))

->where("product_name = '" . $name1 . "' OR product_name = '" . $name2 . "'")

->where("price=?", $price);

I hope that helps. Took me some fooling around to get the strings to work correctly.

Cheers.