MySQL - #1054 - 'childs' 中的未知列 'on clause'

Question

我对复杂的 SQL 查询非常陌生。所以，我正在尝试调试 Magento 生成的 SQL 语句:

SELECT `e`.*, 
       `cat_index`.`position`                                            AS 
       `cat_index_position`, 
       price_index.price                                                 AS 
       `indexed_price`, 
       `price_index`.`price`, 
       `price_index`.`final_price`, 
       IF(`price_index`.`tier_price`, Least(`price_index`.`min_price`, 
                                            `price_index`.`tier_price`), 
       `price_index`.`min_price`)                                        AS 
       `minimal_price`, 
       `price_index`.`min_price`, 
       `price_index`.`max_price`, 
       `price_index`.`tier_price`, 
       GROUP_CONCAT(CONVERT(catalog_product_relation.child_id, CHAR(8))) AS 
       `children`, 
       `sfoi`.`price`                                                    AS 
       `confprice` 
FROM   `catalog_product_entity` AS `e` 
       INNER JOIN `catalog_category_product_index` AS `cat_index` 
               ON cat_index.product_id = e.entity_id 
                  AND cat_index.store_id = 1 
                  AND cat_index.visibility IN( 2, 4 ) 
                  AND cat_index.category_id = '3' 
       INNER JOIN `catalog_product_index_price` AS `price_index` 
               ON price_index.entity_id = e.entity_id 
                  AND price_index.website_id = '1' 
                  AND price_index.customer_group_id = 0 
       INNER JOIN `catalog_product_index_eav` AS `attributeA` 
               ON attributeA.entity_id = e.entity_id 
                  AND attributeA.attribute_id = '184' 
                  AND attributeA.store_id = '1' 
                  AND attributeA.value IN ( 50 ) 
       INNER JOIN `catalog_product_index_eav` AS `attributeB` 
               ON attributeB.entity_id = e.entity_id 
                  AND attributeB.attribute_id = '185' 
                  AND attributeB.store_id = '1' 
                  AND attributeB.value IN ( 95 ) 
       LEFT JOIN `catalog_product_relation` 
              ON e.entity_id = catalog_product_relation.parent_id 
       LEFT JOIN `catalog_product_flat_1` AS `sfoi` 
              ON sfoi.entity_id = `children` 
GROUP  BY `e`.`entity_id` 
ORDER  BY `confprice` DESC 
LIMIT  9 

一切正常，直到:

LEFT JOIN `catalog_product_flat_1` AS `sfoi` 
       ON sfoi.entity_id = `children`

我收到以下错误:

#1054 - Unknown column 'children' in 'on clause'

我看过类似的帖子，但我自己似乎无法弄清楚。请帮助我。

编辑:

生成此查询的 PHP 代码:

$this->_collection->getSelect()->
    joinLeft(
      'catalog_product_relation',
      'e.entity_id = catalog_product_relation.parent_id',
      'GROUP_CONCAT(CONVERT(catalog_product_relation.child_id, CHAR(8))) as children'
);
$this->_collection->getSelect()->joinLeft('catalog_product_flat_1 AS sfoi',
    'sfoi.entity_id = children',
    'sfoi.price AS confprice'
)->order('confprice desc');

实际上，我正在尝试连接两个表(catalog_product_relation 和catalog_product_flat_1)。但加入第一个表后我无法访问“children”列。

Answer 1

在ON条件下将相应的列添加到子表中。

ON sfoi.entity_id = catalog_product_relation.child_id