sql to retrieve all order and product details in magento

sql to retrieve all order and product details in magento

July 7, 2026 · Illustrated author avatarBy Stefan Bogdanescu
sql to retrieve all order and product details in magento

Retrieving Order Details and Product Attributes in Magento

As a senior developer working on Magento projects, I frequently get asked about retrieving order details and product attributes from the database. In this post, let’s dive into how you can write an SQL query to fetch all necessary information including order_id, associated products, and their attributes like size, price, and color.

Understanding the Magento Database Structure

Magento has a relational database structure with many tables. The core tables for orders are sales_flat_order, sales_flat_order_address, and sales_flat_order_item. These tables contain basic order information, address details, and ordered items respectively.

However, finding tables related to product attributes requires looking into the catalog module. The main tables here are catalog_product_entity, catalog_product_entity_varchar, catalog_eav_attribute, etc. These tables store general product information and their attribute values.

Your Current Query

Let’s analyze your current SQL query:

SELECT
items.order_id AS order_id,
concat(address.firstname,' ',address.lastname) as name,
address.email AS Email,
items.created_at AS Date,
orders.base_subtotal as sub_order_total,
orders.base_grand_total as order_total,
address.city as order_city,
address.telephone as phone_number,
address.region as order_region,
items.row_total_incl_tax as product_price,
items.name as product_name,
items.description as product_description

FROM
sales_flat_order_address AS address
JOIN
sales_flat_order AS orders
ON orders.entity_id = address.parent_id
JOIN sales_flat_order_item AS items
  ON items.order_id = orders.entity_id

WHERE orders.status = 'complete'
group by 1,2,3,4,5,6,7,8,9,10,11,12
order by 1;

This query fetches basic order information along with associated products. However, it doesn’t include the product attributes you mentioned (size, price, and color).

Retrieving Product Attributes

To retrieve product attributes, we need to join additional tables. Here’s an updated query:

SELECT
items.order_id AS order_id,
concat(address.firstname,' ',address.lastname) as name,
address.email AS Email,
items.created_at AS Date,
orders.base_subtotal as sub_order_total,
orders.base_grand_total as order_total,
address.city as order_city,
address.telephone as phone_number,
address.region as order_region,
pve.value AS size,   -- Assuming 'size' is a product attribute
pvpa.value AS color,  -- Assuming 'color' is a product attribute
items.row_total_incl_tax as product_price,
items.name as product_name,
items.description as product_description

FROM
sales_flat_order_address AS address
JOIN
sales_flat_order AS orders
ON orders.entity_id = address.parent_id
JOIN sales_flat_order_item AS items
  ON items.order_id = orders.entity_id
LEFT JOIN catalog_product_entity AS ppe
  ON ppe.entity_id = items.product_id
LEFT JOIN catalog_product_entity_varchar AS pve
  ON ppe.entity_id = pve.entity_id AND pve.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'size')   -- Assuming size is a varchar attribute
LEFT JOIN catalog_product_entity_varchar AS pvpa
  ON ppe.entity_id = pvpa.entity_id AND pvpa.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'color')

WHERE orders.status = 'complete'
group by 1,2,3,4,5,6,7,8,9,10,11,12
order by 1;

This query uses LEFT JOIN to include product attributes. Note that you need to replace 'size' and 'color' with the actual attribute codes in your Magento setup.

Conclusion: Retrieving Order Details and Product Attributes

Retrieving order details and product attributes from a Magento database involves joining multiple tables. The main tables are sales_flat_order, sales_flat_order_address, and sales_flat_order_item for order information, while the catalog module’s tables like catalog_product_entity and catalog_product_entity_varchar store product attributes.

Remember to always test your queries on a development or staging environment before running them on a production site. If you find yourself needing more complex queries or facing performance issues, consider using Magento’s built-in API or module functions instead of raw SQL.

At Laravel Company, we often help clients optimize their database queries and improve Magento performance through strategic coding practices and infrastructure optimizations. Don’t hesitate to reach out if you need further assistance with your e-commerce development needs!

Catalog Database Extensions Email

Our network