magento grid cannot find the column when I filter

magento grid cannot find the column when I filter

September 25, 2026 · Illustrated author avatarBy Stefan Bogdanescu
magento grid cannot find the column when I filter

Magento Grid: Resolving the “Cannot Find Column” Filter Error

When working with custom grids in Magento, you may encounter an error when filtering: “SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘note_count’ in ‘where clause’.” This issue often arises due to a misconfiguration in the collection class that handles the grid data.

The Root Cause of the Problem

In your provided code, you are correctly joining two tables using Magento’s built-in join method. However, when it comes to filtering, Magento expects the columns you use in your SELECT clause (in this case, “note_count”) to exist as physical columns in the database table specified in the FROM clause.

The issue stems from the fact that “note_count” is a calculated value derived from a sub-select, not an actual column in the “quote” table. When Magento attempts to filter records based on the note count, it cannot find this column in the main table and throws the error you’re encountering.

Resolving the Issue: A Practical Solution

To resolve this problem, we need to ensure that the calculated value is correctly represented as a virtual column during the initialization of the collection. Here’s how you can modify your code:

Step 1: Add the Virtual Column to the Collection

In your Ssi_Crm_Model_Mysql4_Quote_Collection class, add the following line before the _initSelect() method:

protected function _construct()
{
    $this->addFieldToFilter('note_count', array('lt' => 10)); // Example filter
    $this->_init('crm/quote');
}

This code tells Magento to expect a virtual column named “note_count” and allows you to filter on it before the main query is executed.

Step 2: Update the _joinUserSet Method

Now, update the _joinUserSet() method as follows:

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', 'email')
        );

    return $this;
}

Step 3: Rebuild the Index

After making these changes, ensure that your index is rebuilt to reflect the new virtual column:

php magento indexer:reindex crm_quote

Best Practices for Handling Filterable Calculated Values

To avoid this issue in the future, consider the following best practices:

  1. Use Indexers: When dealing with complex calculations that need to be filterable, use Magento’s built-in indexers to build the necessary indexes and optimize performance.

  2. Virtual Columns: If you must use virtual columns for filtering, ensure they are correctly defined before the main query is executed, as shown in Step 1 above.

  3. Testing: Always test your filters thoroughly after making changes to ensure they work as expected across different scenarios.

In Conclusion

Resolving the “Cannot Find Column” error in Magento grids requires a clear understanding of how filters interact with collections and queries. By correctly defining virtual columns and ensuring they are available before filtering, you can maintain a robust and efficient data grid system.

Remember that optimizing performance is crucial when dealing with complex queries involving multiple joins and calculated values. Consider using indexers to minimize the impact on your store’s load times and ensure smooth user experiences.

For more in-depth coverage of Magento development best practices, visit Laravel Company.

Database Admin Configuration Errors

Our network