Magento: Group By Clause on Date Field
Are you facing issues while trying to fetch the total number of orders along with SUM, MIN, MAX, and AVG of ‘grand_total’ for specified dates in Magento? You’re not alone! Let’s dive into a common query challenge that many developers encounter.
The Current Approach
The developer provided an initial query using Magento’s collection methods:
$collection->getSelect()
->columns( 'SUM(base_grand_total) AS total' )
->columns( 'COUNT(*) AS orders_count' )
->columns( 'DATE_FORMAT(created_at, "%d") AS order_day' )
->columns( 'DATE_FORMAT(created_at, "%d/%m/%y") AS order_date' )
->columns( 'AVG(base_grand_total) AS avg_total' )
->columns( 'MAX(base_grand_total) AS max_total' )
->columns( 'MIN(base_grand_total) AS min_total' )
->where( 'DATE_FORMAT(created_at, "%m") = ?', $month )
->where( 'DATE_FORMAT(created_at, "%Y") = ?', $year )
->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' );
The Issue at Hand
The developer noticed a discrepancy between the query results and the built-in Sales > Order grid in Magento. While the custom grid showed 3 orders on the 26th of the specified month, Magento’s default grid displayed only one order for that date.
Upon investigation, the developer discovered that the created_at field is stored as a timestamp (e.g., “2014-09-04 02:50:04”) in the database. However, when formatted, it appears as “Sep 3, 2014 4:50:04 PM.”
The Root Cause
The primary issue lies within the groupBy clause of the query. When grouping by a date field, Magento expects the date to be in a consistent format across all rows. In the provided example, the developer is using the DATE_FORMAT function twice in the WHERE clause with different formats.
The Recommended Solution
To resolve this issue, you should apply a consistent date format when grouping by the created_at field. Here’s an updated version of the query:
$collection->getSelect()
->columns( 'SUM(base_grand_total) AS total' )
->columns( 'COUNT(*) AS orders_count' )
->where('MONTH(created_at)', $month)
->where('YEAR(created_at)', $year)
->group( 'DATE_FORMAT(created_at, "%m-%d-%y")' );
Key Takeaways
- Ensure consistency in date formatting when grouping by a date field in Magento.
- Avoid using multiple
DATE_FORMATfunctions with different formats within the same query. - Utilize built-in MySQL functions like
MONTH()andYEAR()for better performance.
By following these best practices, you should be able to fetch accurate results from your custom queries without discrepancies between your custom grid and Magento’s default Sales > Order grid.
Happy coding! Don’t forget to explore more Magento development insights on LaravelCompany.com.