Issue Type: Improvement Created: 2006-07-01T09:58:52.000+0000 Last Updated: 2007-07-05T14:43:12.000+0000 Status: Resolved Fix version(s): - 0.8.0 (21/Feb/07)
Reporter: Ron Korving (abraxas) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db_Select
Related issues: Attachments:
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:
<pre class="highlight"> (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.
Posted by Ron Korving (abraxas) on 2006-07-03T09:15:52.000+0000
added multiline solution example
Posted by Ron Korving (abraxas) on 2006-07-03T09:20:58.000+0000
added ``` tags and renamed whereOr() and whereAnd() to or() and and(), because it could also be useful for "having" and possibly other areas.
Posted by Bill Karwin (bkarwin) on 2006-11-13T15:22:38.000+0000
Changing fix version to 0.8.0.
Posted by Bill Karwin (bkarwin) on 2007-01-03T13:27:49.000+0000
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.
Posted by Bill Karwin (bkarwin) on 2007-01-05T17:02:21.000+0000
Recategorize in Zend_Db_Select component.
Posted by Bill Karwin (bkarwin) on 2007-02-07T21:13:55.000+0000
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.
Have you found an issue?
See the Overview section for more details.