mysql - where 子句中的列 'available_for_order' 不明确

Question

我正在 prestashop 中处理这个查询，我知道它可以通过更改为 sa.available_for_order 轻松修复，但这种方式会破坏其他核心文件的逻辑，有没有其他方法可以解决此问题，而无需将 available_for_order 重命名为 sa.available_for_order ，

SELECT
  a.`id_product`,
  b.`name` AS `name`,
  `reference`,
  a.`price` AS `price`,
  sa.`active` AS `active`,
  `newfield`,
  shop.`name` AS `shopname`,
  a.`id_shop_default`,
  image_shop.`id_image` AS `id_image`,
  cl.`name` AS `name_category`,
  sa.`price`,
  0 AS `price_final`,
  a.`is_virtual`,
  pd.`nb_downloadable`,
  sav.`quantity` AS `sav_quantity`,
  sa.`active`,
  IF(sav.`quantity` <= 0, 1, 0) AS `badge_danger`,
  sa.`available_for_order` AS `available_for_order`
FROM
  `ps_product` a
LEFT JOIN
  `ps_product_lang` b
ON
  (
    b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1
  )
LEFT JOIN
  `ps_stock_available` sav
ON
  (
    sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0
  )
JOIN
  `ps_product_shop` sa
ON
  (
    a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default
  )
LEFT JOIN
  `ps_category_lang` cl
ON
  (
    sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default
  )
LEFT JOIN
  `ps_shop` shop
ON
  (
    shop.id_shop = a.id_shop_default
  )
LEFT JOIN
  `ps_image_shop` image_shop
ON
  (
    image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default
  )
LEFT JOIN
  `ps_image` i
ON
  (
    i.`id_image` = image_shop.`id_image`
  )
LEFT JOIN
  `ps_product_download` pd
ON
  (pd.`id_product` = a.`id_product`)
WHERE
  1 AND `available_for_order` = 1
ORDER BY
  a.`id_product` ASC
LIMIT 0, 50

预购

        public function __construct()
        {
            parent::__construct();
    $this->_select .= ',sa.`available_for_order` AS `available_for_order`, ';
             $this->fields_list['sa!available_for_order'] = array(
                'title' => $this->l('Available for order'),
                'width' => 90,
                'active' => 'available_for_order',
                'filter_key' => 'sa!available_for_order',
                'type' => 'bool',
                'align' => 'center',
                'orderby' => false
            );
}

Answer 1

修改您的字段列表以使用过滤器。

$this->fields_list['available_for_order'] = array(
            'title' => $this->l('Available for order'),
            'width' => 90,
            'active' => 'available_for_order',
            'filter_key' => 'available_for_order',
            'havingFilter' => true,
            'type' => 'bool',
            'align' => 'center',
            'orderby' => false
        );

这将在查询中使用 HAVING 而不是 WHERE。

WHERE 子句不适用于列别名，但 HAVING 可以。