sql - 在 Magento 中实现 SQL 函数 having()

Question

我需要一个在 Magento 中“具有”的 SQL 函数。据我所知，没有having功能。

所以我尝试在集合类中实现它

 public function addAttributeHaving($attribute)
 {
      $this->getSelect()->having($attribute); 
      return $this;
 } 

当我使用这个函数时

 $collectionHotel->addAttributeToSort($order, $dir)
                 ->addAttributeHaving('MIN(`apptha_booking_room_types`.`room_price_per_night`) >= '.$lowestprice)
                ->addAttributeHaving('MIN(`apptha_booking_room_types`.`room_price_per_night`) <= '.$highestprice); 

输出查询没问题:

SELECT 
    `e` . *,
    MIN(`apptha_booking_room_types`.room_price_per_night) AS `lowestprice`,
    IF(at_status.value_id > 0,
        at_status.value,
        at_status_default.value) AS `status`,
    IF(at_apptha_hotel_period_to.value_id > 0,
        at_apptha_hotel_period_to.value,
        at_apptha_hotel_period_to_default.value) AS `apptha_hotel_period_to`,
    `price_index`.`price`,
    `price_index`.`tax_class_id`,
    `price_index`.`final_price`,
    IF(price_index.tier_price IS NOT NULL,
        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`
FROM
    `catalog_product_entity` AS `e`
        LEFT JOIN
    `apptha_booking_room_types` ON (apptha_booking_room_types.entity_id = e.entity_id)
        INNER JOIN
    `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
        AND (`at_status_default`.`attribute_id` = '89')
        AND `at_status_default`.`store_id` = 0
        LEFT JOIN
    `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`)
        AND (`at_status`.`attribute_id` = '89')
        AND (`at_status`.`store_id` = 1)
        INNER JOIN
    `catalog_product_entity_datetime` AS `at_apptha_hotel_period_to_default` ON (`at_apptha_hotel_period_to_default`.`entity_id` = `e`.`entity_id`)
        AND (`at_apptha_hotel_period_to_default`.`attribute_id` = '168')
        AND `at_apptha_hotel_period_to_default`.`store_id` = 0
        LEFT JOIN
    `catalog_product_entity_datetime` AS `at_apptha_hotel_period_to` ON (`at_apptha_hotel_period_to`.`entity_id` = `e`.`entity_id`)
        AND (`at_apptha_hotel_period_to`.`attribute_id` = '168')
        AND (`at_apptha_hotel_period_to`.`store_id` = 1)
        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
WHERE
    (`e`.`type_id` = 'hotel')
        AND (IF(at_status.value_id > 0,
        at_status.value,
        at_status_default.value) = '1')
        AND (IF(at_apptha_hotel_period_to.value_id > 0,
        at_apptha_hotel_period_to.value,
        at_apptha_hotel_period_to_default.value) >= '2012-11-09')
GROUP BY `e`.`entity_id`
HAVING (MIN(`apptha_booking_room_types`.`room_price_per_night`) >= 0)
    AND (MIN(`apptha_booking_room_types`.`room_price_per_night`) <= 999999999)
ORDER BY `lowestprice` asc

当我在 MySQL 中执行此查询时，输出正常。

但是当我尝试使用

 $collectionHotel->load(); 

显示此错误:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘apptha_booking_room_types.room_price_per_night’ in ‘having clause’

我已经尽我所能，但我还不能解决这个问题。谁能帮忙？

或者，是否有更好的方法来实现 having() 函数？

Answer 1

我相信到目前为止，这是 magento 的一个错误..好吧...我的问题通过使用一些技巧解决了...但不要使用这种方法，因为 magento 总是说未知列，尽管输出查询很好。