Magento Sales Order tables

Magento Sales Order tables

August 27, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento Sales Order tables

Retrieving Magento Customers Who Purchased Specific Products

Hello! I’m excited to help you navigate through the Magento database to retrieve customers who have purchased specific products. Let’s break down your question and address each point.

Understanding the Current Query

Your current SQL query is a great starting point:

SELECT o.customer_id,i.product_id ,i.NAME
FROM sales_flat_order_item i INNER JOIN sales_flat_order o
ON o.entity_id = i.order_id
WHERE o.customer_id IS NOT NULL
AND product_id IN ('8', '14', '15', '16');

This query joins the sales_flat_order and sales_flat_order_item tables to find orders where the customer ID is not null, and the order contains at least one of the specified products (IDs 8, 14, 15, or 16).

Filtering by Payment Status

You’re rightfully concerned about filtering customers whose payment has been approved. Unfortunately, Magento doesn’t have a direct column for this status in the sales_flat_order table you’re using. The closest field is the state column, which represents the order status.

To get orders with approved payments (assuming ‘processing’ or ‘complete’ status), modify your query like this:

SELECT o.customer_id,i.product_id ,i.NAME
FROM sales_flat_order_item i INNER JOIN sales_flat_order o
ON o.entity_id = i.order_id
WHERE o.customer_id IS NOT NULL
AND o.state IN ('processing', 'complete')
AND product_id IN ('8', '14', '15', '16');

Handling Recurring Payments

For recurring payments, the data flow is slightly different. Magento tracks these in the sales_flat_recurring_profile table.

If you want to include customers who have purchased via recurring profiles:

SELECT o.customer_id,i.product_id ,i.NAME
FROM sales_flat_order_item i INNER JOIN (
  SELECT po.parent_id as order_id, po.state
  FROM sales_flat_recurring_profile po
  WHERE po.status = 'active'
) o ON o.order_id = i.order_id
WHERE o.customer_id IS NOT NULL AND o.state IN ('processing', 'complete')
AND product_id IN ('8', '14', '15', '16');

This subquery joins the sales_flat_recurring_profile table to get active recurring profiles, then retrieves the corresponding order IDs. We filter these orders by payment status.

Best Practices and Further Reading

While direct SQL queries can be powerful, Magento provides robust APIs for most data retrieval needs. Consider using the Magento API (especially if you’re working with larger datasets) or the Zend_Db class for cleaner, more maintainable code.

For a deeper dive into Magento’s database structure and query optimization techniques, check out these resources:

Conclusion

In summary, to retrieve customers who’ve purchased specific products with approved payments (including recurring profiles), you can modify your SQL query as shown above. Remember to replace the product IDs and status values according to your store’s configuration.

This should give you a solid foundation for working with Magento’s order data. The key is to understand the relationships between the sales_flat_order, sales_flat_recurring_profile, and other relevant tables.

Feel free to reach out if you have any further questions or need help with specific scenarios!

Catalog Database Orders Customers

Our network