Skip to content

Instantly share code, notes, and snippets.

@varinen
Created October 1, 2013 21:04
Show Gist options
  • Save varinen/6785074 to your computer and use it in GitHub Desktop.
Save varinen/6785074 to your computer and use it in GitHub Desktop.
An SQL query generated by Magento's tax calculation resource model on a shop that uses the FireGento_GermanSetup extension. This query fetches tax rates that satisfy a tax request object with key 1|3|1|DE|79|12345 (store ID = 1, customer tax class ID = 3, product tax class id = 1, country code = DE, region id = 79 "Niedersachsen", and postcode =…
(SELECT
`main_table`.`tax_calculation_rate_id`,
`main_table`.`tax_calculation_rule_id`,
`main_table`.`customer_tax_class_id`,
`main_table`.`product_tax_class_id`,
`rule`.`priority`,
`rule`.`position`,
`rate`.`rate` AS `value`,
`rate`.`tax_country_id`,
`rate`.`tax_region_id`,
`rate`.`tax_postcode`,
`rate`.`tax_calculation_rate_id`,
`rate`.`code`,
IF(title_table.value IS NULL,
rate.code,
title_table.value) AS `title`
FROM
`tax_calculation` AS `main_table`
INNER JOIN
`tax_calculation_rule` AS `rule` ON `rule`.`tax_calculation_rule_id` = main_table.tax_calculation_rule_id
INNER JOIN
`tax_calculation_rate` AS `rate` ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id
LEFT JOIN
`tax_calculation_rate_title` AS `title_table` ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id
AND title_table.store_id = '1'
WHERE
(customer_tax_class_id = 3)
AND (product_tax_class_id IN ('1'))
AND (rate.tax_country_id = 'DE')
AND (rate.tax_region_id IN (0 , 79))
AND (rate.zip_is_range IS NULL)
AND (rate.tax_postcode IS NULL
OR rate.tax_postcode IN ('*' , '',
'12345',
'12345*',
'1234*',
'123*',
'12*',
'1*'))) UNION (SELECT
`main_table`.`tax_calculation_rate_id`,
`main_table`.`tax_calculation_rule_id`,
`main_table`.`customer_tax_class_id`,
`main_table`.`product_tax_class_id`,
`rule`.`priority`,
`rule`.`position`,
`rate`.`rate` AS `value`,
`rate`.`tax_country_id`,
`rate`.`tax_region_id`,
`rate`.`tax_postcode`,
`rate`.`tax_calculation_rate_id`,
`rate`.`code`,
IF(title_table.value IS NULL,
rate.code,
title_table.value) AS `title`
FROM
`tax_calculation` AS `main_table`
INNER JOIN
`tax_calculation_rule` AS `rule` ON `rule`.`tax_calculation_rule_id` = main_table.tax_calculation_rule_id
INNER JOIN
`tax_calculation_rate` AS `rate` ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id
LEFT JOIN
`tax_calculation_rate_title` AS `title_table` ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id
AND title_table.store_id = '1'
WHERE
(customer_tax_class_id = 3)
AND (product_tax_class_id IN ('1'))
AND (rate.tax_country_id = 'DE')
AND (rate.tax_region_id IN (0 , 79))
AND (rate.zip_is_range IS NOT NULL)
AND ('12345' BETWEEN rate.zip_from AND rate.zip_to)) ORDER BY `priority` ASC , `tax_calculation_rule_id` ASC , `tax_country_id` DESC , `tax_region_id` DESC , `tax_postcode` DESC , `value` DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment