CODEWITHSUNDEEP
magentomagento-1.4
Chris
Chris

Reputation: 1731

magento grid cannot find the column when I filter

I have a custom module that has it's own grid. One of the fields that I pull into the grid is a count of records (notes) that are associated with the entry. It works fine and shows the count in the grid, it sorts fine too, but when I filter I get a message saying it cannot find the column.

Here is the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'note_count' in 'where clause'

And here is the code

class Ssi_Crm_Model_Mysql4_Quote_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{

    protected function _construct()
    {
        $this->_init('crm/quote');
    }

    protected function _initSelect()
    {
        parent::_initSelect();
        $this->_joinUserSet();
        return $this;
    }

    protected function _joinUserSet()
    {
        $this->getSelect()
         ->columns("(SELECT COUNT(note) FROM mage_crm_notes WHERE entity_id = main_table.quote_id AND entity_type = 'quote') as note_count")
            ->join(
                array('user'=>$this->getTable('admin/user')),
                'main_table.user_id=user.user_id',
                array('username' => 'user.username', 'email' => 'user.email'));


        return $this;

    }

Upvotes: 0

Views: 3432

Answers (2)

clockworkgeek
clockworkgeek

Reputation: 37700

One way to deal with this (and there must be others) is to work with the collection rather than the query so that it knows how to filter correctly.

Instead of getSelect()->columns() try addExpressionAttributeToSelect()

$this->addExpressionAttributeToSelect('note_count',
    '(SELECT COUNT(note) FROM mage_crm_notes WHERE entity_id = main_table.quote_id AND entity_type = "quote")',
    ''
)
->joinTable(
    array('user'=>'admin/user'),
    'user_id=user_id',
    array('username' => 'user.username', 'email' => 'user.email')
);

If not working with an EAV collection an unsightly way to achieve the same effect as addExpressionAttributeToSelect() is:

$this->_map['fields']['note_count'] = '(SELECT COUNT(note) FROM mage_crm_notes WHERE entity_id = main_table.quote_id AND entity_type = "quote")';

Upvotes: 0

Stackman
Stackman

Reputation: 469

Chris, you cannot use Aliases in a mySql Where clause which is why you are getting the error when trying to filter the results. This is not a mySql bug, but see a similar query at http://bugs.mysql.com/bug.php?id=1505

Say your sql was...

SELECT field1, field2, (SELECT COUNT(note) FROM mage_crm_notes WHERE entity_id = main_table.quote_id AND entity_type = 'quote') as note_count FROM mage_crm

You'd get your expected three columns of...

: field1 : field2 : note_count :

but you can't have...

SELECT field1, field2, (SELECT COUNT(note) FROM mage_crm_notes WHERE entity_id = main_table.quote_id AND entity_type = 'quote') as note_count FROM mage_crm WHERE note_count > 5

as you'll get the Unknown column 'note_count' in 'where clause' error.

Depending on how you're filtering, you may be able to use the HAVING clause. Have a search through StackOverflow for similar queries (eg. Can you use an alias in the WHERE clause in mysql?)

Upvotes: 1

Related Questions