Implement SQL function having() in Magento

Question

I need a SQL function "having" in Magento. As far as I know, there is no having function.

So I try to implement it in collection class

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

and when I use this function

 $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);

output query is fine:

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

When I execute this query in MySQL, the output is fine.

But when I try to use

 $collectionHotel->load();

this error shows up:

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

I tried everything I could but I can't solve this problem yet. Can anyone help?

Alternatively, are there better ways to implement the having() function?

score 2 · Answer 1 · answered Nov 13 '12 at 01:17

2

i believe as this far, this is a bug of magento.. well... my problem solved by using some trick.. but not use this method because magento always say unknown column although the output query is good.

answered Nov 13 '12 at 01:17

sayasemut

51
1
3

Hi, I need to add having to my query too, could you post, please, which solution you have found? Thanks. – Kostanos Jul 12 '13 at 16:02
hi kostanos, sry i just read your message just now, i found an other way to implement having, easy way, get your collection, and use this function $collection->getSelect()->having($attribute); hope this help... – sayasemut Jul 26 '13 at 07:23

MagePal Extensions · Answer 2 · 2012-11-12T01:37:16.467

0

Try running the sql manually against the db and change this line

....
HAVING (MIN(`apptha_booking_room_types`.`room_price_per_night`) >= 0)
   AND (MIN(`apptha_booking_room_types`.`room_price_per_night`) <= 999999999)

to

.....
HAVING (`lowestprice` >= 0)
   AND (`lowestprice` <= 999999999)

If the above code work then try changing this

->addAttributeHaving('`lowestprice` >= '.$lowestprice)
->addAttributeHaving('`lowestprice` <= '.$highestprice);

edited Nov 12 '12 at 01:37

answered Nov 12 '12 at 01:31

MagePal Extensions

17,646
2
47
62

1

hello .. thankyou for the answer.. i have changed the query into HAVING (`lowestprice` >= 0) AND (`lowestprice` <= 999999999) the output is good in mysql but in magento the error is same SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lowestprice' in 'having clause' – sayasemut Nov 12 '12 at 01:42
What sql does it product now? – MagePal Extensions Nov 12 '12 at 01:48
the output sql is good, nothings wrong.. when i try to execute the output in mysql, it works.. – sayasemut Nov 12 '12 at 02:06

score 0 · Answer 3 · edited Nov 02 '16 at 05:29

0

Another way to write having and order clause

$product_log_data = Mage::getModel('customtabs/productlog')->getCollection();
 $product_log_data->getSelect()->having(
            "rowNumber = 1" );
 $product_log_data->getSelect()->order(
            "rowNumber" );

edited Nov 02 '16 at 05:29

Manashvi Birla

2,837
3
14
28

answered Sep 11 '14 at 07:09

Anisha Shaikh

200
4

score 0 · Answer 4 · answered Mar 22 '15 at 16:40

Unfortunately this is a bug in Magento. I had the same issue, and ended up creating a second DB model class, just for retrieving the data for admin area grid display.

In the constructor method of the second Collection.php file I ended up creating a mysql VIEW using a direct DB query, selecting the data (with a group by clause) from the original table to temporary cache the results.

In the construct method:

            $resource = Mage::getSingleton('core/resource');
            $writeConnection = $resource->getConnection('core_write');

            $sql = "
                   CREATE OR REPLACE VIEW your_temp_view_name AS
                   SELECT SUM(column_name) as column_name
                   FROM original_table_name
                   WHERE ...
                   GROUP BY customer_id
            ";

            $writeConnection->query($sql);

Because the data is already grouped and the aggregating functions have already been executed, you can then just use normal addAttributeToFilter methods to filter the data.

It's a bit hacky, but it worked in this case. Depending on the amount of data and the amount of simultaneous users, it might or might not work.

score 0 · Answer 5 · answered Nov 01 '16 at 18:03

First of all $countSelect->reset(Zend_Db_Select::HAVING); means it will reset HAVING from your collection. That means it will remove the having clause. And Its not what you want. You may want to add it to the collection (app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php->_getSelectCountSql() here.)

But the main culprit it the getSize() method which exists in lib/Varien/Data/Collection/Db.php file.

I tried almost any solution available on internet but none of them worked for me.

Now i did the below.

public function getSize()
{
    if (is_null($this->_totalRecords)) {
        //$sql = $this->getSelectCountSql();
        $sql = $this->getSelect();
        $this->_totalRecords = count($this->getConnection()->fetchAll($sql, $this->_bindParams));
    }
    return intval($this->_totalRecords);
}

Check i am not even using the getSelectCountSql(). I am just reading the whole SQL QUERY and fetching all data and return the count of it. That's all.

Implement SQL function having() in Magento

5 Answers5