I think what you are looking for is to be able to use Zend_Db_Expr() inside
of the from method of the select object.
Effectively, it would be:
$query->from(
array('L' => 'sales_opportunity'),
array('L.*',
'assigned_to_name' => new Zend_Db_Expr($DB->select()->...)
)
...
That should allow for the expression to pass through without quoting. Try
that, if that doesn't work I'll dig a little deeper, this might require a
feature request in the issue tracker.
-ralph
On 1/5/09 12:10 PM, "Saeven" <[email protected]> wrote:
>
> Hi,
>
> The following snippet of code is what I intuitively would have thought would
> solve the requirement of subqueries:
>
> $query = Engine::$DB->select();
> $query->from(
> array( 'L' => 'sales_opportunities' ),
> array(
> "L.*",
> "assigned_to_name" => Engine::$DB->select()
> ->from( array( "U" => "users" ) )
> ->columns( "CONCAT( U.first_name, ' ', U.last_name )" )
> ->where( "U.id = L.assigned_to" ),
> "account_name" => Engine::$DB->select()
> ->from( array( "A" => 'sales_accounts' ) )
> ->columns( "A.name" )
> ->where( "A.id = L.sales_account_id" )
> )
> );
>
>
> The goal is to hammer out a query such as:
>
> SELECT L.*,
> (SELECT CONCAT( U.first_name, ' ', U.last_name ) FROM users U WHERE U.id =
> L.assigned_to ) AS assigned_to_name,
> (SELECT A.name FROM sales_accounts A WHERE A.id = L.sales_account_id )
> AS account_name
> FROM sales_opportunities L
> WHERE ....
>
>
> The snippet of code above, however, generates invalid SQL:
>
> SELECT `L`.*, `L`.`SELECT ``U``.*, CONCAT( U.first_name, ' ', U.last_name )
> FROM ``users`` AS ``U`` WHERE (U.id = L.assigned_to)` AS `assigned_to_name`,
> `L`.`SELECT ``A``.*, ``A``.``name`` FROM ``sales_accounts`` AS ``A`` WHERE
> (A.id = L.sales_account_id)` AS `account_name` FROM `sales_opportunities` AS
> `L`
>
> Double quoting, not respecting subqueries by prefixing them with the table
> alias, and not adding parentheses where necessary (adding the latter should
> be a given when adding select expressions?).
>
>
> Am I going against the grain here? ;)
>
> Thanks for the help.
> Alex
--
Ralph Schindler
Software Engineer | [email protected]
Zend Framework | http://framework.zend.com/
