Issue using "having" in Magento collection

Question

I am trying to build a custom collection for a grid in Magento admin module. I have created a new collection method called "addAttributeHaving" which just does the following:

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

See collection code:

$collection->addFieldToSelect(
    array(
        'entity_id',
        'created_at',
        'increment_id',
        'customer_email',
        'customer_firstname',
        'customer_lastname',
        'grand_total',
        'status'
    )
);

$collection->getSelect()->joinLeft(array('sfop' => 'sales_flat_order_payment'), 'main_table.entity_id = sfop.parent_id', 'sfop.amount_authorized');
$collection->getSelect()->columns('sum(sfop.amount_authorized) AS AUTHD');
$collection->getSelect()->columns('grand_total - sum(sfop.amount_authorized) AS DIF_AU');
$collection->addFieldToFilter('main_table.state', array('in' => array('new','payment_review')));
$collection->addFieldToFilter('main_table.sd_order_type', array('neq' => 7));
$collection->addFieldToFilter('sfop.method', array('neq' => 'giftcard'));
$collection->addFieldToFilter('main_table.created_at', array('gt' => $this->getFilterDate()));
$collection->getSelect()->group(array('main_table.entity_id'));
$collection->addAttributeHaving('DIF_AU <> 0');
$collection->load(true,true);

$this->setCollection($collection);

This produces the following SQL which executes perfectly fine and produces the expected results when ran outside of Magento.

[METHOD=Varien_Data_Collection_Db->printLogQuery] SELECT `main_table`.`entity_id`, `main_table`.`entity_id`, `main_table`.`created_at`, `main_table`.`increment_id`, `main_table`.`customer_email`, `main_table`.`customer_firstname`, `main_table`.`customer_lastname`, `main_table`.`grand_total`, `main_table`.`status`, `sfop`.`amount_authorized`, sum(sfop.amount_authorized) AS `AUTHD`, grand_total - sum(sfop.amount_authorized) AS `DIF_AU` FROM `sales_flat_order` AS `main_table` LEFT JOIN `sales_flat_order_payment` AS `sfop` ON main_table.entity_id = sfop.parent_id WHERE (main_table.state in ('new', 'payment_review')) AND (main_table.sd_order_type != 7) AND (sfop.method != 'giftcard') AND (main_table.created_at > '2013-04-07') GROUP BY `main_table`.`entity_id` HAVING (DIF_AU <> 0)

However, when I try to load the grid inside Magento I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DIF_AU' in 'having clause'

Additionally, if I remove the having clause (which breaks my results), I am able to use the DIF_AU column for a datasource in the Grid.

Answer 1

I am going to actually answer my own question here. I know, tacky, but I stumbled upon the answer when looking much closer at the actual stack trace. The collection is loading fine however, the failure comes a little later in execution when we try to get the collection count in Varien_Data_Collection_Db::getSelectCountSql(). The SQL that is produced from this is:

SELECT COUNT(*) FROM sales_flat_order AS main_table LEFT JOIN sales_flat_order_payment AS sfop ON main_table.entity_id = sfop.parent_id WHERE (main_table.state in ('payment_review')) AND (main_table.sd_order_type != 7) AND (sfop.method != 'giftcard') AND (main_table.created_at > '2013-04-07') GROUP BY main_table.entity_id HAVING (DIF_AU <> 0)

You will notice that the HAVING statement is attached yet we have no definition for the DIF_AU column any longer. It appears as though I will need to extend a custom getSelectCountSql() in my collection class to get the right record count.

I have created an extended getSelectCountSql() in the custom collection class that adds back in the missing column required for the having statement.

public function getSelectCountSql()
  {
    $countSelect = parent::getSelectCountSql();
    $countSelect->columns('grand_total - sum(sfop.amount_authorized) AS DIF_AU');
    $countSelect->reset(Zend_Db_Select::GROUP);
    return $countSelect;
  }

Answer 2

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 the above solution mentioned by @Anthony but that didnt work.

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.

Answer 3

I fixed this issue here: app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php:943 add this: $select->reset(Zend_Db_Select::HAVING);

Just copy app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php to app/code/local/Mage/Catalog/Model/Resource/Product/Collection.php

My code looks now like this:

/**
 * Build clear select
 *
 * @param Varien_Db_Select $select
 * @return Varien_Db_Select
 */
protected function _buildClearSelect($select = null)
{
    if (is_null($select)) {
        $select = clone $this->getSelect();
    }
    $select->reset(Zend_Db_Select::ORDER);
    $select->reset(Zend_Db_Select::LIMIT_COUNT);
    $select->reset(Zend_Db_Select::LIMIT_OFFSET);
    $select->reset(Zend_Db_Select::COLUMNS);
    $select->reset(Zend_Db_Select::HAVING);

Answer 4

This solution will work if your select has unique columns names because any columns in the subquery select list must have unique names

Subquery: table subquery allows duplicate column names

public function getSelectCountSql()
{
    $this->_renderFilters();
    $select = clone $this->getSelect();
    $select->reset(Zend_Db_Select::ORDER);
    $select->reset(Zend_Db_Select::LIMIT_COUNT);
    $select->reset(Zend_Db_Select::LIMIT_OFFSET);        

    $countSelect = clone $this->getSelect();
    $countSelect->reset();
    $countSelect->from(array('a' => $select), 'COUNT(*)');
    return $countSelect;
}

PS: This answer is for general magento collections. not related to only product collection.

Answer 5

This is working

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