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.

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

Solution 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

Solution 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:

Comments

[Luiz Damim]

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

[Ballsacian1]

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

[AD.]

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