Last active
February 17, 2017 01:05
-
-
Save jonathonbyrdziak/4163ea935765fa37621bab8541345001 to your computer and use it in GitHub Desktop.
Common Magento SQL Queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#### 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