ZF-182: Zend_Db_Select::where() and ::orWhere() are insufficient

Description

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:

select * from mytable where a=3 and (a=b or (a=c and a=d))

A syntax like the following could be a solution:



(or)

$select->where( $select->and( 'a=3', $select->or( 'a=b', $select->and('a=c', 'a=d') ) ) ); ```

So I don't really have an elegant solution, but the current situation really is insufficient if you ask me. Also, looking at the current implementation of Zend_Db_Select, it seems like you hardly use the adapter in __toString(), but I guess you're well aware of this and it will improve in the future (Oracle, unlike MySQL for example, uses "(+)" for certain join syntax).

You might find this interesting by the way: http://troels.arvin.dk/db/rdbms/

Bill: sounds reasonable, thanks.

Comments

added multiline solution example

added ``` tags and renamed whereOr() and whereAnd() to or() and and(), because it could also be useful for "having" and possibly other areas.

Changing fix version to 0.8.0.

Regarding joins, all RDBMS brands now support ANSI SQL-92 syntax for JOIN...ON.
Zend_Db should not support the proprietary join syntax forms used by Oracle and Microsoft.

Regarding complex expressions, I agree that the method interface does not support arbitrary expressions.

There are many other SQL features for which Zend_Db does not offer a method interface. Subqueries, UNION, FOR UPDATE, etc. Also RDBMS-proprietary clauses such as index hints, locking modes. It would be cost-prohibitive to support all of the SQL language with a method interface.

The current solution to write queries that are not supported in the method interface is to use the query() method. You have complete control to write your own SQL query.

Recategorize in Zend_Db_Select component.

The where() and orWhere() methods of Zend_Db_Select support a majority of common queries. This class is not able to implement a method interface to the entire SQL language. Complex queries that are not supported by Zend_Db_Select must be written in SQL and submitted as a string to the Zend_Db_Adapter_*::query() method.