Skip to content

Instantly share code, notes, and snippets.

@PorraphitChuasuk

PorraphitChuasuk/Category_Attributes.sql

Forked from grafikchaos/Category_Attributes.sql
Created June 18, 2019 09:43
Show Gist options
  • Save PorraphitChuasuk/07a302d7ad29e8a2347bfc85e7fa1f68 to your computer and use it in GitHub Desktop.
Save PorraphitChuasuk/07a302d7ad29e8a2347bfc85e7fa1f68 to your computer and use it in GitHub Desktop.
Download ZIP
Magento EAV SQL Queries
Raw
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
);
Raw
Customer_Attributes.sql
SET @customerid = (SELECT e.entity_id FROM customer_entity e WHERE e.email = '[email protected]');
-- 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
);
Raw
Order.sql
SELECT sfo.*
FROM sales_flat_order sfo
WHERE sfo.increment_id = 'MY_ORDER_NUMBER';
Raw
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
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment