convert Raw SQL to Zend_Db_Select

I need to convert this sql query to Zend_Db_Select object

'SELECT `main_table`.*, `product_name_table`.`name` as `product_name`, `product_price_table`.`price` as `product_price`,
    COUNT(main_table.answer_id) AS `answer_count`, 
    (SELECT CONCAT(main_table.answer_title, ":::", GROUP_CONCAT(DISTINCT main_table.query SEPARATOR "###"))) AS `answer_title_with_query` 
FROM `nanorepwidgets_answer` AS `main_table`
LEFT JOIN (SELECT `e`.`entity_id`, `at_name`.`value` AS `name` 
        FROM `catalog_product_entity` AS `e` 
        INNER JOIN `catalog_product_entity_varchar` AS `at_name` 
            ON (`at_name`.`entity_id` = `e`.`entity_id`) 
            AND (`at_name`.`attribute_id` = "'.$name_id.'") 
            AND (`at_name`.`store_id` = '.$store.')) AS `product_name_table`
        ON (`main_table`.`product_id` = `product_name_table`.`entity_id`)
LEFT JOIN (SELECT `e`.`entity_id`, `at_price`.`value` AS `price`  
        FROM `catalog_product_entity` AS `e` 
        INNER JOIN `catalog_product_entity_decimal` AS `at_price` 
                ON (`at_price`.`entity_id` = `e`.`entity_id`) 
                AND (`at_price`.`attribute_id` = "'.$price_id.'") 
                AND (`at_price`.`store_id` = '.$store.')) AS `product_price_table`
        ON (`main_table`.`product_id` = `product_price_table`.`entity_id`)

GROUP BY `main_table`.`answer_id`, `main_table`.`product_id` 
ORDER BY `answer_id` ASC'

How can I do the nested selects in the "join" ? any suggestion and help will be greatly appreciated.

Solution

Zend_Db_Select has a __toString() method, so I'm pretty sure you can construct the nested select for the join, and then use it in the main query. Something like this...

// $adapter is your Zend_Db_Adapter...
$joinConditionName = '`at_name`.`entity_id` = `e`.`entity_id`' 
    . $adapter->quoteInto('`at_name`.`attribute_id` = ?', $name_id)
    . $adapter->quoteInfo('`at_name`.`store_id` = ?', $store);
$subQueryName = new Zend_Db_Select();
$subQueryName->from(array('e' => 'catalog_product_entity'))
    ->join(
         'at_name' => 'catalog_product_entity', 
         $joinConditionName
    )->columns(array('e.entity_id', 'at_name.value'));


// and then the main query
$query = new Zend_Db_Select();
$query->from('main_table' => 'nanorepwidgets_answer')
    ->joinLeft(array('product_name_table' => $subQueryName->__toString()),
        'main_table.product_id = produce_name_table.entity_id')

Or something along those lines should work, I think. It's not documented but reading the source (1.12) you can pass a Zend_Db_Select as the first argument to joinLeft, and your subquery would then need to define the alias. All comes to the same thing, I think.

You could also make the quoting easier in the sub-select by using a simpler ON clause (at_name.entity_id = e.entity_id) and then using ->where('at_name.attribute_id = ?', $name_id). That'd remove the need for the $joinConditionName bit.

I'm pretty sure I've done this, although tbh I can't find it right now, so apologies if the detail isn't quite right.