How do I add complex where clause to Zend Table Select?

Question

I searched the Web and could not find anything that would show me a good solid example. My question is basically this:

How do I convert this:

SELECT * FROM table WHERE ((a = 1 AND b = 2) OR (c = 3 OR c = 4)) AND d = 5;

To Zend syntax similar to this:

$this ->select() ->from($this->_schema.'.'.$this->_name) ->where('a = ?', '1');

So how can it be done?

Thank a lot in advance.

Traveled

Answer 1

I had a similar problem. See the code example in the answer here: Grouping WHERE clauses with Zend_Db_Table_Abstract

So you would end up with something like:

$db = $this->getAdapter();
$this->select()
     ->where('(' . $db->quoteInto('a = ?', 1) . ' AND ' . $db->quoteInto('b = ?', 2) . ') OR (' . $db->quoteInto('c = ?', 3) . ' OR ' . $db->quoteInto('c = ?', 4) . ')')
     ->where('d = ?', 5);

Which would give you:

SELECT `table_name`.* FROM `table_name` WHERE ((a = 1 AND b = 2) OR (c = 3 OR c = 4)) AND (d = 5)

Regenerator

Answer 2

1) Build a condition for all groups Where/orWhere:

$conditions = $this->select()
        ->where('a= ?', 5)
        ->orWhere('b= ?', 6)
        ->getPart(Zend_Db_Select::WHERE);
// result: $conditions = "(a= 5) OR (b= 6)";

Use getPart() method to get the where condition.

2) Next, reset the where part of current select object:

$this->select()->reset(Zend_Db_Select::WHERE);

3) Finally, use where condition as you want:

$this->select()
    ->where('d= ?', 5)
    ->where(implode(' ', $conditions));

http://framework.zend.com/manual/1.12/ru/zend.db.select.html

Agueda

Answer 3

Per a message board post on the Zend Framework website, this may not be possible.

It seems to me that where() and orWhere() in the Zend_Db_Select class are not enough to be able to write all queries. It does not support the nesting of conditions, which doesn't enforce the user with abstraction in somewhat more complex cases. With where() and orWhere() I cannot write this:

Cooper

Answer 4

Edit

The array functionality of Zend_Db_Select->where is designed only for using it with the IN clause.

Example #17 Example of an array parameter in the where() method
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (product_id IN (1, 2, 3))

$productIds = array(1, 2, 3);

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('product_id IN (?)', $productIds);

Original

As Peder said you can't nest orWhere but you can pass multiple arguments into where and orWhere.

$this->select()
  ->from($this->_schema.'.'.$this->_name)
  ->where(' ( a = ? AND b = ? ) OR ( c = ? OR c = ? ) ', array(1,2,3,4))
  ->where('d = ?',array(5));

Heisler

-1. This will replace every ? with the content array(1,2,3,4) resulting in a query SELECT "table"."col" FROM "table" WHERE ( ( a = 1, 2, 3, 4 AND b = 1, 2, 3, 4 ) OR ( c = 1, 2, 3, 4 OR c = 1, 2, 3, 4 ) ) AND (d = 5) – Thaumatology

You are correct. I seem to remember someone showing this "feature" on Stack Overflow a while back. – Heisler

So, there is no way to do this with where or orWhere functions? – Traveled