19

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.

  • 1
    UPDATE: I was able to track down the issue to the parent getSelectCountSql() method. This is actually where the issue is happening when trying to get the collection count. – Anthony Leach Jr Jul 7 '13 at 21:55
  • 1
    Post an answer! WTH is that sd_order_type coming from? – benmarks Jul 7 '13 at 22:28
  • 1
    Top secret custom order type stuff that has been added to flat tables. I am still working out the answer. – Anthony Leach Jr Jul 7 '13 at 22:29
  • 1
    "Users with less than 10 reputation can't answer their own question for 8 hours after asking. You can answer in 7 hours. Until then please use comments, or edit your question instead." (solution to come in 7 hours). – Anthony Leach Jr Jul 7 '13 at 22:36
  • 1
    Someone give this man an upvote – benmarks Jul 8 '13 at 1:34
11

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;
  }
  • 1
    Not tacky at all… if you figure out the solution first, it's encouraged. Others will potentially find it helpful down the road. +1 :) – davidalger Jul 8 '13 at 16:44
  • You should file a bug report! magentocommerce.com/bug-tracking – benmarks Jul 8 '13 at 18:40
  • This is an issue I have also seen, and well done on finding an answer. However, I do not think your solution is correct - because you are using group by, your SelectCountSql needs to be returning the number of groups. So you need either a count(fetchAll()), or to rewrite your query using count(distinct main_table.entity_id) instead of count(*) – Benubird Sep 12 '13 at 10:54
  • I think you are very likely correct. This project has been on the back burner since this posting, and just now getting back to it. In a demo last week, I noticed incorrect record count being reported in the grid. Will report back my findings once I have resolved it. – Anthony Leach Jr Sep 27 '13 at 13:30
  • @AnthonyLeachJr any news on your findings? – Simon Jun 22 '16 at 9:49
0

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.

0

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

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.