Multiple/nested "select where" with Zend_Db_Select

Question

Hi there

I need to create something like this:

select name from table where active = 1 AND (name LIKE 'bla' OR description LIKE 'bla')

The first part is easy:

$sqlcmd = $db->select()
->from("table", "name")
->where("active = ?", 1)

Now comes the tricky part. How can I nest? I know that I can just write

->orWhere("name LIKE ? OR description LIKE ?", "bla")

But thats wron, because I need to dynamically change all the parts. The query will be built all the time the script runs. Some parts get deleted, some altered. In this example I need to add those OR-s because sometimes I need to search wider. "My Zend Logic" tells me that the correct way is like this:

$sqlcmd = $db->select()
->from("table", "name")
->where("active = ?", 1)
->where(array(
    $db->select->where("name LIKE ?", "bla"),
    $db->select->orWhere("description LIKE ?", "bla")
))

But that doesn't work (atleast I dont remember it working).

Please. Can someone help me to find a object oriented way for nesting "where"-s

Answer 1

Here's an example from the ZF manual

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

It should fit your needs

Answer 2

I had the same issue. See the answer to my question here: http://stackoverflow.com/questions/1179279/grouping-where-clauses-with-zend-db-table-abstract