mysql - Zend MySQL : Unknown Column in Where Clause - when using GROUP_CONCAT()

Question

在 Magento 中，我使用 Zend DDL 创建一个查询，将表值 group_concat() 放入名为“网站”的单个字段中。

查询工作正常，直到添加了 where 子句。

SELECT
  `main_table`.*,
  GROUP_CONCAT(website.website_id) AS `websites`
FROM
  `megamenu_entity` AS `main_table`
   LEFT JOIN `megamenu_website` AS `website`
   ON main_table.entity_id = website.megamenu_id
WHERE
  (websites = '1')
GROUP BY `main_table`.`entity_id`

这给出了错误:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'websites' in 'where clause'

这是为什么呢？谁能帮我解决这个问题吗？最好是在 Zend DDL 中。这就是我现在正在做的事情。

$collection->getSelect()->joinLeft(
  array('website' => $collection->getTable('megamenu/megamenu_website')),
  "main_table.entity_id = website.megamenu_id",
  array('websites' => new Zend_Db_Expr('GROUP_CONCAT(website.website_id)'))
)->group('main_table.entity_id');

谢谢

Answer 1

您的 WHERE 子句将无法识别列别名。列别名可以在 GROUP BY、ORDER BY 和 HAVING 子句中使用。

更新了您的查询以使用原始列名称，例如:

SELECT
  `main_table`.*,
  GROUP_CONCAT(website.website_id) AS `websites`
FROM
  `megamenu_entity` AS `main_table`
   LEFT JOIN `megamenu_website` AS `website`
   ON main_table.entity_id = website.megamenu_id
WHERE
  (website.website_id = '1')
GROUP BY `main_table`.`entity_id`