Magento EAV SQL Queries

Category_Attributes.sql

SET @entityid = 5; -- category's ID

-- Select varchar/string based category attribute values
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_category_entity e
  JOIN catalog_category_entity_varchar eav ON e.entity_id = eav.entity_id
  JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
-- Select integer based category attribute values (includes boolean values)
UNION(
  SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
  FROM catalog_category_entity e
    JOIN catalog_category_entity_int eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  WHERE e.entity_id = @entityid
)
-- Select decimal based category attribute values
UNION(
  SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
  FROM catalog_category_entity e
    JOIN catalog_category_entity_decimal eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  WHERE e.entity_id = @entityid
)
-- Select datetime based category attribute values
UNION(
  SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
  FROM catalog_category_entity e
    JOIN catalog_category_entity_datetime eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  WHERE e.entity_id = @entityid
)
-- Select text based category attribute values
UNION(
  SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
  FROM catalog_category_entity e
    JOIN catalog_category_entity_text eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  WHERE e.entity_id = @entityid
);

Customer_Attributes.sql

SET @customerid = (SELECT e.entity_id FROM customer_entity e WHERE e.email = 'user@example.com');

-- Select varchar (string) based customer attribute values
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type', eaov.value AS 'option_frontend_label'
FROM customer_entity e
  JOIN customer_entity_varchar eav ON e.entity_id = eav.entity_id
  JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
  LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
WHERE e.entity_id = @customerid
-- Select integer based customer attribute values (includes boolean values)
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'VALUE', 'int' AS 'type', eaov.value AS 'option_frontend_label'
  FROM customer_entity e
    JOIN customer_entity_int eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.entity_id = @customerid
)
-- Select decimal based customer attribute values
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'VALUE', 'decimal' AS 'type', eaov.value AS 'option_frontend_label'
  FROM customer_entity e
    JOIN customer_entity_decimal eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.entity_id = @customerid
)
-- Select datetime based customer attribute values
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'VALUE', 'datetime' AS 'type', eaov.value AS 'option_frontend_label'
  FROM customer_entity e
    JOIN customer_entity_datetime eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.entity_id = @customerid
)
-- Select text based customer attribute values
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'VALUE', 'text' AS 'type', eaov.value AS 'option_frontend_label'
  FROM customer_entity e
    JOIN customer_entity_text eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.entity_id = @customerid
);

Order.sql

SELECT sfo.*
FROM sales_flat_order sfo
WHERE sfo.increment_id = 'MY_ORDER_NUMBER';

Produt_Attributes.sql

SET @entitySku = 'WIDGET123';
 
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type', eaov.value AS 'option_frontend_label'
FROM catalog_product_entity e
  JOIN catalog_product_entity_varchar eav ON e.entity_id = eav.entity_id
  JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
  LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
  LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
WHERE e.sku = @entitySku
-- Select integer based attributes (includes boolean values)
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'int' AS 'type', eaov.value AS 'option_frontend_label'
  FROM catalog_product_entity e
    JOIN catalog_product_entity_int eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.sku = @entitySku
)
-- Select decimal based attributes
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type', eaov.value AS 'option_frontend_label'
  FROM catalog_product_entity e
    JOIN catalog_product_entity_decimal eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.sku = @entitySku
)
-- Select datetime based attributes
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type', eaov.value AS 'option_frontend_label'
  FROM catalog_product_entity e
    JOIN catalog_product_entity_datetime eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.sku = @entitySku
)
-- Select text based attributes
UNION(
  SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'text' AS 'type', eaov.value AS 'option_frontend_label'
  FROM catalog_product_entity e
    JOIN catalog_product_entity_text eav ON e.entity_id = eav.entity_id
    JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id
    LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)
    LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
  WHERE e.sku = @entitySku
);