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

Revisions

  1. Darren Felton revised this gist Aug 9, 2016. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions 003 - catalog_category.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,4 @@
    # Shows the sort order value of each attribute in a group.

    SELECT
    `eav_attribute`.`attribute_code`,
    `eav_entity_attribute`.`sort_order`,
    @@ -17,4 +16,8 @@ WHERE
    AND
    `eav_entity_attribute`.`entity_type_id` = '3'
    ORDER BY
    `sort_order`;
    `sort_order`;


    # Show the order of Attribute Groups
    SELECT * FROM `eav_attribute_group` WHERE `attribute_set_id` = '3' ORDER BY `sort_order`;
  2. Darren Felton renamed this gist Aug 9, 2016. 1 changed file with 0 additions and 0 deletions.
    0 000 - Magento SQL Snippets → 000 - Magento SQL Snippets.md
    View file
    File renamed without changes.
  3. Darren Felton revised this gist Aug 9, 2016. 2 changed files with 21 additions and 21 deletions.
    22 changes: 1 addition & 21 deletions 002 - catalog_product.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -44,24 +44,4 @@ 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` '0' # <!-- UNCOMMENT TO FILTER BY PRODUCT ID
    ORDER BY `cpe`.`entity_id` ASC;


    SELECT
    `eav_attribute`.`attribute_code`,
    `eav_entity_attribute`.`sort_order`,
    `eav_attribute_group`.`attribute_group_name`
    FROM
    `eav_entity_attribute`
    LEFT JOIN
    `eav_attribute_group`
    ON `eav_attribute_group`.`attribute_group_id` = `eav_entity_attribute`.`attribute_group_id`
    LEFT JOIN
    `eav_attribute`
    ON `eav_attribute`.`attribute_id` = `eav_entity_attribute`.`attribute_id`
    WHERE
    `eav_attribute_group`.`attribute_group_name` = 'General Information'
    AND
    `eav_entity_attribute`.`entity_type_id` = '3'
    ORDER BY
    `sort_order`;
    ORDER BY `cpe`.`entity_id` ASC;
    20 changes: 20 additions & 0 deletions 003 - catalog_category.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    # Shows the sort order value of each attribute in a group.

    SELECT
    `eav_attribute`.`attribute_code`,
    `eav_entity_attribute`.`sort_order`,
    `eav_attribute_group`.`attribute_group_name`
    FROM
    `eav_entity_attribute`
    LEFT JOIN
    `eav_attribute_group`
    ON `eav_attribute_group`.`attribute_group_id` = `eav_entity_attribute`.`attribute_group_id`
    LEFT JOIN
    `eav_attribute`
    ON `eav_attribute`.`attribute_id` = `eav_entity_attribute`.`attribute_id`
    WHERE
    `eav_attribute_group`.`attribute_group_name` = 'General Information' # Change this to your group name.
    AND
    `eav_entity_attribute`.`entity_type_id` = '3'
    ORDER BY
    `sort_order`;
  4. Darren Felton revised this gist Aug 9, 2016. 2 changed files with 21 additions and 1 deletion.
    2 changes: 1 addition & 1 deletion 001 - database.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,4 +4,4 @@ SELECT
    round(((data_length + index_length) / 1024 / 1024), 2) as "TABLE_SIZE (MB)"
    FROM
    information_schema.TABLES
    WHERE table_schema = "DATABASE_NAME" # <!-- Update this with DB name
    WHERE table_schema = "DATABASE_NAME" # <!-- Update this with DB name
    20 changes: 20 additions & 0 deletions 002 - catalog_product.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -45,3 +45,23 @@ LEFT JOIN `catalog_product_entity_media_gallery` AS `cpemg` ON `cpemgv`.`value_
    LEFT JOIN `catalog_product_entity` AS `cpe` ON `cpe`.`entity_id`=`cpemg`.`entity_id`
    #WHERE `cpe`.`entity_id` '0' # <!-- UNCOMMENT TO FILTER BY PRODUCT ID
    ORDER BY `cpe`.`entity_id` ASC;


    SELECT
    `eav_attribute`.`attribute_code`,
    `eav_entity_attribute`.`sort_order`,
    `eav_attribute_group`.`attribute_group_name`
    FROM
    `eav_entity_attribute`
    LEFT JOIN
    `eav_attribute_group`
    ON `eav_attribute_group`.`attribute_group_id` = `eav_entity_attribute`.`attribute_group_id`
    LEFT JOIN
    `eav_attribute`
    ON `eav_attribute`.`attribute_id` = `eav_entity_attribute`.`attribute_id`
    WHERE
    `eav_attribute_group`.`attribute_group_name` = 'General Information'
    AND
    `eav_entity_attribute`.`entity_type_id` = '3'
    ORDER BY
    `sort_order`;
  5. Darren Felton revised this gist Jun 27, 2016. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions 001 - database.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,7 @@
    # Show Size of tables in MB
    SELECT
    table_name AS "Table",
    round(((data_length + index_length) / 1024 / 1024), 2) as "TABLE_SIZE (MB)"
    FROM
    information_schema.TABLES
    WHERE table_schema = "DATABASE_NAME" # <!-- Update this with DB name
  6. Darren Felton renamed this gist Jun 27, 2016. 1 changed file with 0 additions and 0 deletions.
    0 001 - catalog_product.sql → 002 - catalog_product.sql
    View file
    File renamed without changes.
  7. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion 000 - Magento SQL Snippets
    View file
    Original file line number Diff line number Diff line change
    @@ -4,4 +4,4 @@ All SQL snippets assume there is no database table name prefix configured.
    In the event that your database does have a table nameprefix, you
    should be able to simply update the original table names found in the
    "FROM" and "LEFT JOIN" declarations. All table names are aliased therefore
    a full revision of the SQL snippets are not necessary.
    a full revision of the SQL snippets would not be necessary.
  8. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion 000 - Magento SQL Snippets
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +1,7 @@
    Numerous SQL snippets to reference for pulling data straight from Magento's database.
    Numerous SQL snippets to reference for pulling data straight from Magento's database.

    All SQL snippets assume there is no database table name prefix configured.
    In the event that your database does have a table nameprefix, you
    should be able to simply update the original table names found in the
    "FROM" and "LEFT JOIN" declarations. All table names are aliased therefore
    a full revision of the SQL snippets are not necessary.
  9. Darren Felton renamed this gist Jun 23, 2016. 1 changed file with 0 additions and 0 deletions.
    0 001 - snippets.sql → 001 - catalog_product.sql
    View file
    File renamed without changes.
  10. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@ LEFT JOIN `catalog_product_entity_varchar` AS `cpev` ON `cpev`.`entity_id`=`cpe`
    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 `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`;
    @@ -17,7 +17,7 @@ LEFT JOIN `catalog_product_entity_text` AS `cpet` ON `cpet`.`entity_id`=`cpe`.`e
    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 `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`;
    @@ -29,7 +29,7 @@ LEFT JOIN `catalog_product_entity_int` AS `cpei` ON `cpei`.`entity_id`=`cpe`.`en
    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 `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`;
    @@ -43,5 +43,5 @@ SELECT
    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` '0' # <!-- FILTER BY PRODUCT ID HERE
    #WHERE `cpe`.`entity_id` '0' # <!-- UNCOMMENT TO FILTER BY PRODUCT ID
    ORDER BY `cpe`.`entity_id` ASC;
  11. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,8 +6,8 @@ LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id`=`cpe
    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
    #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
  12. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,8 +18,8 @@ LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id`=`cpet`.
    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
    #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
    @@ -30,8 +30,8 @@ LEFT JOIN `eav_attribute` AS `ea` ON `ea`.`attribute_id`=`cpei`.`
    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
    #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
  13. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    # Select VARCHAR data type values from catalog products
    SELECT `cpe`.`entity_id`, `cpe`.`sku`, `cpev`.`value`
    FROM `catalog_product_entity` AS `cpe`
    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`
    @@ -12,7 +12,7 @@ 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`
    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`
    @@ -24,7 +24,7 @@ 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`
    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`
    @@ -36,10 +36,10 @@ ORDER BY `cpei`.`value`;

    # Select the media gallery information for a product
    SELECT
    `cpe`.`entity_id` AS `product_id`,
    `cpe`.`entity_id` AS `product_id`,
    `cpemgv`.`value_id`,
    `cpemg`.`value` AS `file`,
    `cpemgv`.`label` AS `label`
    `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`
  14. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,6 +42,6 @@ SELECT
    `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`
    LEFT JOIN `catalog_product_entity` AS `cpe` ON `cpe`.`entity_id`=`cpemg`.`entity_id`
    #WHERE `cpe`.`entity_id` '0' # <!-- FILTER BY PRODUCT ID HERE
    ORDER BY `cpe`.`entity_id` ASC;
  15. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 11 additions and 11 deletions.
    22 changes: 11 additions & 11 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,9 @@
    # 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`
    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
    @@ -13,9 +13,9 @@ 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`
    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
    @@ -25,9 +25,9 @@ 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`
    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
    @@ -41,7 +41,7 @@ SELECT
    `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`
    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` '0' # <!-- FILTER BY PRODUCT ID HERE
    ORDER BY `cpe`.`entity_id` ASC;
  16. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -43,5 +43,5 @@ SELECT
    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
    #WHERE `cpe`.`entity_id` '0' # <!-- FILTER BY PRODUCT ID HERE
    ORDER BY `cpe`.`entity_id` ASC;
  17. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -38,7 +38,7 @@ ORDER BY `cpei`.`value`;
    SELECT
    `cpe`.`entity_id` AS `product_id`,
    `cpemgv`.`value_id`,
    `cpemg`.`value` AS `file`,
    `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`
  18. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,10 +36,10 @@ 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`
    `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`
  19. Darren Felton revised this gist Jun 23, 2016. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion 001 - snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -32,4 +32,16 @@ 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`;
    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;
  20. Darren Felton revised this gist Jun 23, 2016. 2 changed files with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions 000 - Magento SQL Snippets
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1 @@
    Numerous SQL snippets to reference for pulling data straight from Magento's database.
    0 magento-snippets.sql → 001 - snippets.sql
    View file
    File renamed without changes.
  21. Darren Felton revised this gist Jun 10, 2016. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion magento-snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,4 +20,16 @@ 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`;
    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`;
  22. Darren Felton revised this gist Jun 7, 2016. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions magento-snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,19 +1,23 @@
    # Select a VARCHAR type
    # 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 a TEXT type
    # 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`;
  23. Darren Felton created this gist Jun 7, 2016.
    19 changes: 19 additions & 0 deletions magento-snippets.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    # Select a VARCHAR type
    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
    ORDER BY `cpev`.`value`;

    # Select a TEXT type
    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
    ORDER BY `cpet`.`value`;