Magento: How to Query Top Selling Products Using SQL
As an experienced developer and technical blogger, I often encounter complex queries like yoursâseeking to retrieve top selling products in Magento using raw SQL. While the task may initially seem straightforward, it requires careful consideration of your database schema and sales processes.
Understanding Your Current Approach
Your modified query is a step in the right direction. It joins key tables such as sales_flat_order_item, sales_flat_order, and catalog_product_entity to calculate the total ordered quantity for each product. The use of subqueries (HAVING (SUM(order_items.qty_ordered) > 0)) ensures only products with positive sales quantities are included.
However, you’ve noticed discrepancies between this query’s results and Magento’s built-in top sellers report in the admin panel. This gap is likely due to differences in filtering criteria or how “top” sellers are defined.
Optimizing Your Query for Top Sellers
To align your SQL output with Magento’s admin report, consider the following optimizations:
-
Filter by Order Status: Ensure you only include orders that have been successfully processed and shipped (e.g., status not equal to ‘canceled’). You can add this condition as a WHERE clause:
WHERE order.state != 'canceled'. -
Consider Grouped and Configurable Products: If your store uses grouped or configurable products, these should be included in the top sellers list. Remove the commented-out lines related to these product types.
-
Handle Option Items: If your store offers products with options (e.g., size, color), make sure these are correctly accounted for in your query. You might need to join additional tables like
sales_flat_order_item_option. -
Order by Quantity or Sales Amount: Depending on your business needs, you can order the results by total quantity sold (
ORDERED_QTY DESC) or by sales amount (if available). To calculate the sales amount, multiplyorder_items.qty_orderedbyorder_items.price_incl_tax.
A Comprehensive Top Sellers SQL Query
Considering these optimizations, here’s an improved version of your query:
SELECT
SUM(order_items.qty_ordered) AS ordered_qty,
order_items.name AS order_items_name,
order_items.product_id AS entity_id,
e.entity_type_id,
e.attribute_set_id,
e.type_id,
e.sku,
e.has_options,
e.required_options,
e.created_at,
e.updated_at
FROM
sales_flat_order_item AS order_items
INNER JOIN
sales_flat_order AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state != 'canceled'
LEFT JOIN
catalog_product_entity AS e ON
-- (e.type_id NOT IN ('grouped', 'configurable'))
AND e.entity_id = order_items.product_id AND e.entity_type_id = 4
WHERE
(parent_item_id IS NULL)
GROUP BY
order_items.product_id
HAVING
(SUM(order_items.qty_ordered) > 0)
ORDER BY
ORDERED_QTY DESC;
Final Thoughts and Recommendations
While this optimized query should provide a more accurate representation of your top sellers, it’s essential to remember that SQL queries can have performance implications, especially on large datasets.
For better performance and easier customization, consider using Magento’s built-in API or creating an extension that leverages Magento’s business logic for calculating top sellers. This approach ensures you benefit from the framework’s optimizations and maintainability.
If you need further assistance with optimizing your database queries or building extensions in Magento, I invite you to explore our services at Laravel Company, where we specialize in helping businesses unlock the full potential of their e-commerce platforms through expert development and consultation.