Resolving the “Column not found” Error in Magento 2.4.2 Sales Order Grid
As a seasoned developer and technical blogger, I understand the frustration that comes with encountering errors like this one in Magento 2.x. In this article, we will delve into the cause of your issue and provide a step-by-step guide to resolve the “Column not found: 1054 Unknown column ‘allocated_sources’ in ‘order clause’” error in the Sales Order Grid.
Understanding the Issue
The error message is quite straightforward - it indicates that the MySQL query is attempting to sort (ORDER BY) by a column named allocated_sources, which cannot be found in the sales_order_grid table. This issue arises because you have added a custom column, “Allocated Sources”, to the grid but haven’t updated the database or modified the SQL queries accordingly.
Why Does This Happen?
When you customize your Sales Order Grid, Magento expects that the changes are reflected in the database schema as well. The sales_order_grid table is responsible for the data displayed on the admin panel’s order view page and grid. When a new column is added to this table, it must be recognized by the SQL queries used to fetch and display the data.
Resolving the Issue
Step 1: Check Your Custom Column
Ensure that you have correctly defined your “Allocated Sources” column in the sales_order_grid table. The column name should match exactly with what is being referenced in the SQL query causing the error.
Step 2: Update the Database Schema
To align the database schema with your customization, follow these steps:
-
Open a MySQL client and connect to your Magento installation’s database.
-
Run the following command to add the
allocated_sourcescolumn to thesales_order_gridtable:ALTER TABLE `sales_order_grid` ADD COLUMN `allocated_sources` VARCHAR(255) NOT NULL DEFAULT ''; -
Verify that the column has been added by running a SHOW COLUMNS command or checking the database’s structure.
Step 3: Update the Collection File
Open the file responsible for generating the Sales Order Grid data - typically located at app/code/Vendor/Module/etc/adminhtml/di.xml. Look for the section related to the grid collection and ensure that it includes your custom column in the select statement.
Here’s an example of what this might look like:
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
<arguments>
<argument name="collections" xsi:type="array">
<item name="sales_order_grid" xsi:type="string">Vendor_Module\Model\ResourceModel\SalesOrder\Grid\Collection</item>
</argument>
</arguments>
</type>
</config>
In the Collection file (e.g., app/code/Vendor/Module/Model/ResourceModel/SalesOrder/Grid/Collection.php), update the _renderFiltersBeforeLoad() method to include your custom column in the select statement:
protected function _renderFiltersBeforeLoad()
{
// Add your custom column here...
$this->getSelect()->joinLeft(
['sales_order_grid' => $this->getTable('sales_order_grid')],
'main_table.entity_id = sales_order_grid.entity_id',
['allocated_sources']
);
}
Step 4: Clear Cache and Reindex
After making these changes, clear your Magento cache and reindex the data.
-
Open a terminal window and navigate to your Magento installation directory.
-
Run the following commands:
php bin/magento cache:clean php bin/magento indexer:reindex
Conclusion
In this blog post, we explored a common issue faced by Magento 2 developers - an error in the Sales Order Grid due to an unrecognized column. We traced the root cause of the problem and provided a comprehensive solution that involves updating the database schema and collection file.
Remember, whenever you customize the grid or add new columns, always ensure that the changes are reflected in both the database structure and the relevant SQL queries used by the Magento system.
If you’re looking to learn more about Magento 2 development, I highly recommend checking out our comprehensive guide on Magento 2 Development at Laravel Company. This guide covers everything from installation to advanced customization techniques, ensuring you have the skills needed to tackle any challenge that comes your way.
Happy coding!