Hi Ralph,
Unfortunately that doesn't work; this code:
************
$query->from(
array( 'L' => 'sales_opportunities' ),
array(
"L.*",
"assigned_to_name" => new Zend_Db_Expr(
Engine::$DB->select()
->from( array( "U" => "users" ),
array() )
->columns( "CONCAT( U.first_name, ' ',
U.last_name )" )
->where( "U.id = L.assigned_to" )
),
"account_name" => new Zend_Db_Expr(
Engine::$DB->select()
->from( array( "A" => 'sales_accounts'
), array( "name" ) )
->where( "A.id = L.sales_account_id" )
)
)
);
************
Produces the following:
SELECT `L`.*, SELECT CONCAT( U.first_name, ' ', U.last_name ) FROM `users`
AS `U` WHERE (U.id = L.assigned_to) AS `assigned_to_name`, SELECT `A`.`name`
FROM `sales_accounts` AS `A` WHERE (A.id = L.sales_account_id) AS
`account_name` FROM `sales_opportunities` AS `L`
Closer though! Still missing parentheses...
Ralph Schindler-2 wrote:
>
> 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/
>
>
>
>
--
View this message in context:
http://www.nabble.com/Subquery-Generation-tp21296085p21310269.html
Sent from the Zend Framework mailing list archive at Nabble.com.
