0

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;

    }
Chris
  • 1,731
  • 3
  • 24
  • 38
  • could you provide the structure of mage_crm_notes? Also, in the `columns()` line you seem to be joining with another table, but it's not clear which: could you explain the query? – OSdave Dec 09 '11 at 09:47

2 Answers2

1

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?)

Community
  • 1
  • 1
Stackman
  • 439
  • 4
  • 6
  • Yeah good through, only problem is that the magento base code renders the filter with a where and I'm not about to go override. – Chris Dec 14 '11 at 21:34
0

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")';
clockworkgeek
  • 37,650
  • 9
  • 89
  • 127