mysql - 将原始 SQL 转换为 Zend_Db_Select

Question

我需要将此 sql 查询转换为 Zend_Db_Select 对象

'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'

如何在“join”中进行嵌套选择？任何建议和帮助将不胜感激。

Answer 1

Zend_Db_Select 有一个 __toString() 方法，所以我很确定您可以为连接构造嵌套选择，然后在主查询中使用它。像这样的事情...

// $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')

或者类似的东西应该有效，我认为。它没有记录，但阅读源代码(1.12)，您可以将 Zend_Db_Select 作为第一个参数传递给 joinLeft，然后您的子查询需要定义别名。我认为，所有的结果都是一样的。

您还可以通过使用更简单的 ON 子句 (at_name.entity_id = e.entity_id)，然后使用 ->where('at_name.attribute_id = ?', $name_id) 来使子选择中的引用更容易。这将消除对 $joinConditionName 位的需要。

我很确定我已经做到了这一点，尽管我现在找不到它，所以如果细节不太正确，我深表歉意。