Skip to content

Instantly share code, notes, and snippets.

@dfelton

dfelton/000 - Magento SQL Snippets.md

Last active November 22, 2021 15:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dfelton/e6b5c01e5937cfe0bcba2c9624ee4cd7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dfelton/e6b5c01e5937cfe0bcba2c9624ee4cd7 to your computer and use it in GitHub Desktop.
Download ZIP
Useful SQL snippets for a Magento store.
Raw
000 - Magento SQL Snippets
Numerous SQL snippets to reference for pulling data straight from Magento's database.
Raw
001 - snippets.sql
# Select VARCHAR data type values from catalog products
SELECT `cpe`.`entity_id`, `cpe`.`sku`, `cpev`.`value`
FROM `catalog_product_entity` AS `cpe`
LEFT JOIN `catalog_product_entity_varchar` AS `cpev` ON `cpev`.`entity_id`= `cpe`.`entity_id`
LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id` = `cpev`.`attribute_id`
LEFT JOIN `eav_entity_type` AS `eat` ON `eat`.`entity_type_id` = `ea`.`entity_type_id`
WHERE `eat`.`entity_type_code` = 'catalog_product'
AND `ea`.`attribute_code` = 'name' # <- REPLACE WITH YOUR ATTRIBUTE CODE
#AND `cpe`.`entity_id` = '' # <-- UNCOMMENT TO FILTER BY PRODUCT ID
#AND `cpe`.`sku` = '' # <-- UNCOMMENT TO FILTER BY SKU
ORDER BY `cpev`.`value`;
# Select TEXT data type values from catalog products
SELECT `cpe`.`entity_id`, `cpe`.`sku`, `cpet`.`value`
FROM `catalog_product_entity` AS `cpe`
LEFT JOIN `catalog_product_entity_text` AS `cpet` ON `cpet`.`entity_id`= `cpe`.`entity_id`
LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id` = `cpet`.`attribute_id`
LEFT JOIN `eav_entity_type` AS `eat` ON `eat`.`entity_type_id` = `ea`.`entity_type_id`
WHERE `eat`.`entity_type_code` = 'catalog_product'
AND `ea`.`attribute_code` = 'description' # <- REPLACE WITH YOUR ATTRIBUTE CODE
#AND `cpe`.`entity_id` = '' # <-- UNCOMMENT TO FILTER BY PRODUCT ID
#AND `cpe`.`sku` = '' # <-- UNCOMMENT TO FILTER BY SKU
ORDER BY `cpet`.`value`;
# Select INT data type values from catalog products
SELECT `cpe`.`entity_id`, `cpe`.`sku`, `cpei`.`value`
FROM `catalog_product_entity` AS `cpe`
LEFT JOIN `catalog_product_entity_int` AS `cpei` ON `cpei`.`entity_id`= `cpe`.`entity_id`
LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id` = `cpei`.`attribute_id`
LEFT JOIN `eav_entity_type` AS `eat` ON `eat`.`entity_type_id` = `ea`.`entity_type_id`
WHERE `eat`.`entity_type_code` = 'catalog_product'
AND `ea`.`attribute_code` = 'color' # <- REPLACE WITH YOUR ATTRIBUTE CODE
#AND `cpe`.`entity_id` = '' # <-- UNCOMMENT TO FILTER BY PRODUCT ID
#AND `cpe`.`sku` = '' # <-- UNCOMMENT TO FILTER BY SKU
ORDER BY `cpei`.`value`;
# Select the media gallery information for a product
SELECT
`cpe`.`entity_id` AS `product_id`,
`cpemgv`.`value_id`,
`cpemg`.`value` AS `file`,
`cpemgv`.`label` AS `label`
FROM `catalog_product_entity_media_gallery_value` AS `cpemgv`
LEFT JOIN `catalog_product_entity_media_gallery` AS `cpemg` ON `cpemgv`.`value_id` = `cpemg`.`value_id`
LEFT JOIN `catalog_product_entity` AS `cpe` ON `cpe`.`entity_id` = `cpemg`.`entity_id`
#WHERE `cpe`.`entity_id` IS NULL # <!-- FILTER BY PRODUCT ID HERE
ORDER BY `cpe`.`entity_id` ASC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment