Magento Sales Order Grid Joining Table

Magento Sales Order Grid Joining Table

August 15, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento Sales Order Grid Joining Table

Solving Magento Sales Order Grid Joining Challenges

As an experienced Magento developer, I often encounter queries about joining the order items table to the sales order grid for filtering purposes. In this post, we will address a common issue related to grouping and filtering joined collections in Magento.

The provided code snippet shows a successful implementation of joining the sales/order_item table to the main collection using the join() method. However, when attempting to add a column filter to the joined collection, an error occurs due to the use of an undefined alias (names) in the having clause.

Understanding the Error

The error message “Column not found: 1054 Unknown column ‘main_table.names’ in ‘having clause…” indicates that MySQL is unable to recognize the names column because it was defined using a Zend_Db_Expr object during the collection preparation but not explicitly referenced as an alias later.

Resolving the Filter Issue

To resolve this issue, we need to ensure that we reference the correct aliases when applying filters. In our case, the names column is a grouped concatenated value, and using an alias like main_table.names in the having clause causes the error.

Here’s how to modify the _addColumnFilterToCollection() method to correctly apply filters:

protected function _addColumnFilterToCollection($column)
{
    if ($this->getCollection() && $column->getFilter()->getValue()) {
        if ($column->getId() == 'skus') {
            $this->getCollection()
                ->join(
                    'sales/order_item',
                    '`sales/order_item`.order_id=`main_table`.entity_id',
                    array('skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR "|")'))
                )
                ->getSelect()
                ->group('`main_table`.entity_id')
                ->having('find_in_set(?, `main_table`.skus)', $column->getFilter()->getValue());

            return $this;
        }
        if ($column->getId() == 'names') {
            $this->getCollection()
                ->join(
                    'sales/order_item',
                    '`sales/order_item`.order_id=`main_table`.entity_id',
                    array('names' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ",")'))
                )
                ->getSelect()
                ->group('`main_table`.entity_id')
                ->having('find_in_set(?, `main_table`.names)', $column->getFilter()->getValue());

            return $this;
        }
    }

    return parent::_addColumnFilterToCollection($column);
}

In the modified code, we have replaced main_table.names with main_table.skus and main_table.skus for the having clauses, ensuring that we reference the correct aliases defined during the collection preparation.

Best Practices for Joining and Filtering Magento Collections

  1. Always define clear and descriptive aliases for joined tables to avoid confusion.
  2. Use Zend_Db_Expr objects sparingly and ensure they are correctly referenced in subsequent operations.
  3. Test your filters thoroughly, especially when dealing with grouped or concatenated data.

Conclusion

In this post, we explored the challenges of joining Magento order items to the sales order grid for filtering purposes. By understanding the error message and following the provided solution, you can successfully implement column filters on joined collections without encountering unknown column errors.

Remember that proper alias management is crucial when dealing with complex SQL queries in Magento, especially when working with grouped or concatenated data.

For more advanced Magento development techniques and best practices, be sure to check out our comprehensive guides at Laravel Company.

Database Admin Orders Errors

Our network