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
--
View this message in context:
http://www.nabble.com/Subquery-Generation-tp21296085p21296085.html
Sent from the Zend Framework mailing list archive at Nabble.com.
