Magento 1.8 – Remove Existing WHERE Clauses from $collection->getSelect()

collection;magento-1.8query

I am trying to modify the Lifetime Sales figure on the admin landing page to include pending_payment orders. I have set up a module as follows:

class Steamdesk_Dashboard_Block_Dashboard_Sales extends Mage_Adminhtml_Block_Dashboard_Sales

And I have the following to set a suitable WHERE clause in the SQL for the collection:

$collection->getSelect()->where('main_table.state NOT IN (?)', array(
        Mage_Sales_Model_Order::STATE_CLOSED,
        Mage_Sales_Model_Order::STATE_CANCELED
    )
);

However there are already two elements in the WHERE clause array which will cause the above to be ignored, which I can see if I check $collection->getSelect() as follows:

[where] => Array
(
    [0] => (main_table.status NOT IN('canceled'))
    [1] => AND (main_table.state NOT IN('new', 'pending_payment'))
)

I know all this code is being run, but my question is how can I clear the existing attributes in the collection's generated SQL query?

Best Answer

Simply add $collection->clear()->getSelect()->reset(\Zend_Db_Select::WHERE); to your code above your Query.

Related Solutions

Magento Collection – Difference Between getSize() and count()

Most (if not all) the collections extend Varien_Data_Collection_Db. Here are the 2 methods from this class

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

public function count() //inherited from Varien_Data_Collection
{
    $this->load();
    return count($this->_items);
}

There is a difference. For getSize() the collection is not loaded. For count() it is. Usually collection models use the same getSize() method as above and only override getSelectCountSql().
In getSelectCountSql() the limit is reset in order to get the total number of records available for the set filters (where statement). See how the getSelectCountSql() works

public function getSelectCountSql()
{
    $this->_renderFilters();
    $countSelect = clone $this->getSelect();
    $countSelect->reset(Zend_Db_Select::ORDER);
    $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
    $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
    $countSelect->reset(Zend_Db_Select::COLUMNS);
    $countSelect->columns('COUNT(*)');
    return $countSelect;
}

Magento – getSelect Question

Your question doesn't have enough context to provide a concrete answer, so I'm going to explain why Magento is throwing an exception with the message

Cannot determine the field name

Hopefully that will give you enough information to track down the problem.

In a stock Magento system, the only place the error "Cannot determine the field name" appears is

#File: app/code/core/Mage/Sales/Model/Resource/Collection/Abstract.php
protected function _attributeToField($attribute)
{
    $field = false;
    if (is_string($attribute)) {
        $field = $attribute;
    } elseif ($attribute instanceof Mage_Eav_Model_Entity_Attribute) {
        $field = $attribute->getAttributeCode();
    }
    if (!$field) {
        Mage::throwException(Mage::helper('sales')->__('Cannot determine the field name.'));
    }
    return $field;
}

So, if the _attributeToField method is passed an attribute value that's

An Empty string
An attribute object where getAttributeCode returns false (or one of the many values PHP considers false-ish)
Something that's not an Mage_Eav_Model_Entity_Attribute or string

then the exception "Cannot determine the field name" is thrown.

In a standard system Magento calls this method when you call a collection object's addAttributeToSelect, addAttributeToFilter, addAttributeToSort, or addAttributeToSearchFilter methods.

So, something about your code causes an invalid value to be passed to _attributeToField. This means the problem isn't your sub-query, or the sub-query may alter something that causes an invalid value to be passed.

Best Answer

Related Solutions

Magento Collection – Difference Between getSize() and count()

Magento – getSelect Question

Related Topic