Magento – Issue using “having” in Magento collection

admincollection;gridgrid-serlization

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.

Best Answer

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;
  }
Related Topic