I have a bit of a huge MySQL query that I'm having trouble debugging, when I run EXPLAIN on it, it says I have an impossible where clause.
It's a big query, but its basically just a lot of joins and nothing too complicated. The system is for print based products and allows admins to add products, product attributes (groups of options), product attribute values (the options for each attribute), also paper size and price ranges. Then, printers can add their own prices for all these combinations, this query selects available products, in this case no options have been selected, so using the default attribute values.
This is being built up using codeIgniter and DMZ.
When I remove the lines like "AND `product_type_attribute_2_product_attributes`.`id` = 2", both attributes prices return with the same ID, like maybe the same tables being joined more than once are using the same WHERE clause, but since they are all being renamed and the aliases are given the WHERE clauses, that shouldn't happen?
Hopefully my explanation makes sense and someone can make sense of this beastly query
Thanks!
It's a big query, but its basically just a lot of joins and nothing too complicated. The system is for print based products and allows admins to add products, product attributes (groups of options), product attribute values (the options for each attribute), also paper size and price ranges. Then, printers can add their own prices for all these combinations, this query selects available products, in this case no options have been selected, so using the default attribute values.
This is being built up using codeIgniter and DMZ.
When I remove the lines like "AND `product_type_attribute_2_product_attributes`.`id` = 2", both attributes prices return with the same ID, like maybe the same tables being joined more than once are using the same WHERE clause, but since they are all being renamed and the aliases are given the WHERE clauses, that shouldn't happen?
Hopefully my explanation makes sense and someone can make sense of this beastly query
Code:
SELECT DISTINCT `products`.*, `product_types`.`name` AS product_type_name, `printers`.`display_name` AS printer_display_name, `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`value` as product_type_attribute_1_product_attribute_value_product_attribute_value_prices_value, `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`id` as product_type_attribute_1_product_attribute_value_product_attribute_value_prices_id, `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`value` as product_type_attribute_2_product_attribute_value_product_attribute_value_prices_value, `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`id` as product_type_attribute_2_product_attribute_value_product_attribute_value_prices_id, ( `products`.`base_price` + product_type_attribute_1_product_attribute_value_product_attribute_value_prices.value + product_type_attribute_2_product_attribute_value_product_attribute_value_prices.value) as price_total
FROM (`products`)
LEFT OUTER JOIN `product_types` product_types ON `product_types`.`id` = `products`.`product_type_id`
LEFT OUTER JOIN `printers` printers ON `printers`.`id` = `products`.`printer_id`
LEFT OUTER JOIN `_product_attributes_product_types` product_type_attribute_1__product_attributes_product_types ON `product_types`.`id` = `product_type_attribute_1__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attributes` product_type_attribute_1_product_attributes ON `product_type_attribute_1_product_attributes`.`id` = `product_type_attribute_1__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attribute_values` product_type_attribute_1_product_attribute_values ON `product_type_attribute_1_product_attributes`.`id` = `product_type_attribute_1_product_attribute_values`.`product_attribute_id`
LEFT OUTER JOIN `product_attribute_value_prices` product_type_attribute_1_product_attribute_value_product_attribute_value_prices ON `product_type_attribute_1_product_attribute_values`.`id` = `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`product_attribute_value_id`
LEFT OUTER JOIN `_product_attributes_product_types` product_type_attribute_2__product_attributes_product_types ON `product_types`.`id` = `product_type_attribute_2__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attributes` product_type_attribute_2_product_attributes ON `product_type_attribute_2_product_attributes`.`id` = `product_type_attribute_2__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attribute_values` product_type_attribute_2_product_attribute_values ON `product_type_attribute_2_product_attributes`.`id` = `product_type_attribute_2_product_attribute_values`.`product_attribute_id`
LEFT OUTER JOIN `product_attribute_value_prices` product_type_attribute_2_product_attribute_value_product_attribute_value_prices ON `product_type_attribute_2_product_attribute_values`.`id` = `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`product_attribute_value_id`
WHERE `product_types`.`active` = 1
AND `printers`.`active` = 1
AND `product_type_attribute_1_product_attribute_values`.`default` = 1
AND `product_type_attribute_1_product_attributes`.`id` = 1
AND `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`value` >= 0
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.product_id = `products`.`id`
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.size_id = (SELECT `sizes`.`id` FROM `sizes` WHERE `sizes`.`default` = 1 AND `sizes`.`product_type_id` = `product_types`.`id`)
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.number_range_id = (SELECT `number_ranges`.`id` FROM `number_ranges` WHERE `number_ranges`.`default` = 1 AND `number_ranges`.`product_type_id` = `product_types`.`id`)
AND `product_type_attribute_2_product_attribute_values`.`default` = 1
AND `product_type_attribute_2_product_attributes`.`id` = 2
AND `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`value` >= 0
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.product_id = `products`.`id`
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.size_id = (SELECT `sizes`.`id` FROM `sizes` WHERE `sizes`.`default` = 1 AND `sizes`.`product_type_id` = `product_types`.`id`)
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.number_range_id = (SELECT `number_ranges`.`id` FROM `number_ranges` WHERE `number_ranges`.`default` = 1 AND `number_ranges`.`product_type_id` = `product_types`.`id`)