Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Magento EAV SQL Queries
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
);
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
);
SELECT sfo.*
FROM sales_flat_order sfo
WHERE sfo.increment_id = 'MY_ORDER_NUMBER';
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
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment