Skip to content

Instantly share code, notes, and snippets.

@jonathonbyrdziak
Last active February 17, 2017 01:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jonathonbyrdziak/4163ea935765fa37621bab8541345001 to your computer and use it in GitHub Desktop.
Save jonathonbyrdziak/4163ea935765fa37621bab8541345001 to your computer and use it in GitHub Desktop.
Common Magento SQL Queries
USE dgustore;
SELECT
`e`.`entity_id` as `id`,
`e`.`sku`,
IF(_table_status.value_id > 0, _table_status.value, _table_status_default.value) AS `status`,
IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,
c2.value as category,
IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description`
FROM
`catalog_product_entity` AS `e`
INNER JOIN
`catalog_product_entity_varchar` AS `at_name_default`
ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND
(`at_name_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND
`at_name_default`.`store_id` = 0
LEFT JOIN
`catalog_product_entity_varchar` AS `at_name`
ON (`at_name`.`entity_id` = `e`.`entity_id`) AND
(`at_name`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND
(`at_name`.`store_id` = 1)
INNER JOIN
`catalog_product_entity_text` AS `at_description_default`
ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND
(`at_description_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id WHERE `ea`.`attribute_code` = 'short_description' AND et.entity_type_code = 'catalog_product')) AND
`at_description_default`.`store_id` = 0
LEFT JOIN
`catalog_product_entity_text` AS `at_description`
ON (`at_description`.`entity_id` = `e`.`entity_id`) AND
(`at_description`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id WHERE `ea`.`attribute_code` = 'short_description' AND et.entity_type_code = 'catalog_product')) AND
(`at_description`.`store_id` = 1)
INNER JOIN
`catalog_category_product` as `c1`
ON (`c1`.`product_id` = `e`.`entity_id`)
LEFT JOIN
`catalog_category_entity_varchar` as `c2`
ON `c1`.`category_id` = `c2`.`entity_id` AND
`c2`.`attribute_id` = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 3)
INNER JOIN
`catalog_product_entity_int` AS `_table_status_default`
ON (`_table_status_default`.`entity_id` = `e`.`entity_id`)
AND (`_table_status_default`.`attribute_id` = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status' AND entity_type_id = 4))
AND `_table_status_default`.`store_id` = 0
LEFT JOIN
`catalog_product_entity_int` AS `_table_status`
ON (`_table_status`.`entity_id` = `e`.`entity_id`)
AND (`_table_status`.`attribute_id` = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status' AND entity_type_id = 4))
AND (`_table_status`.`store_id` = '1')
WHERE (IF(_table_status.value_id > 0, _table_status.value, _table_status_default.value) = '2')
AND `e`.`type_id` = 'configurable'
-- GROUP BY e.entity_id
;
#### Generate a report for all orders which were charged taxes in a particular state.
```sql
SELECT DISTINCT item.order_id, ord.customer_email, ord.customer_firstname, ord.customer_lastname,
ra.street, ra.city, ra.region, ra.postcode, ra.telephone,
ord.base_subtotal_invoiced, ord.base_tax_amount, ord.base_shipping_amount, ord.base_total_invoiced,
ord.shipping_tax_amount
FROM magento.sales_flat_order_address as ra
LEFT JOIN magento.sales_flat_order_item as item
ON ra.parent_id = item.order_id
LEFT JOIN magento.sales_flat_order as ord
ON item.order_id = ord.entity_id
where (item.tax_percent > 0 OR ord.base_tax_amount <> "" OR ord.shipping_tax_amount <> "") and (ra.region = 'Texas' or ra.region_id = '57')
GROUP BY ord.entity_id
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment