Magento Question posted in
The official documentation can be found here.

sql to retrieve all order and product details in magento

I want to retrieve all details related to an order such as order_id, products related to that order and product attributes such as size, price and color in magento database. This is the query I have currently used to retrieve details about the order. but I am unable to find the tables that have details about the product attributes. 

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;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT 
    ce.sku,
    ea.attribute_id,
    ea.attribute_code,
    ce_int.value as value,
    ea.is_required AS required,
    eaov.value AS product_option
FROM catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea 
    ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_int AS ce_int 
    ON ce.entity_id = ce_int.entity_id 
    AND ea.attribute_id = ce_int.attribute_id
LEFT JOIN eav_attribute_option_value as eaov
    ON ce_int.value = eaov.option_id
WHERE ce.entity_id = 1111 and ea.attribute_code in ('color','size')

  2. 0

    Please use following query. This may help you –

    SELECT  
            CONCAT(address.firstname,' ',
            address.lastname) AS Name,
            address.email AS Email,
            items.created_at AS Date,
            items.name AS Description,
            items.store_id AS Logon,
            items.name AS Category,
            items.store_id AS FeedbackDate,              
            items.sku AS ProductSearchcode,
            items.order_id AS Orderref  
       FROM sales_flat_order AS orders
      JOIN sales_flat_order_item AS items 
      ON items.order_id = orders.entity_id 
      LEFT JOIN sales_flat_order_address AS address
      ON orders.entity_id = address.parent_id

  WHERE orders.status = 'complete'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search