Im trying to get a list of products from our database, grouped on pattern ,and brand and using MIN() to get the lowest price of each pattern, Im using the query builder in codeIgniter 3 to build the following query which almost works as needed, except that the product ID does not match the lowest priced product, so when you click on a product rather than it going to the lowest priced product, it will go to another priced product albeit from the same brand and pattern.
I know I need to use some kind of subquery, but everything i've tried so far seems to not give the correct number of products back? im just not sure how to apply it to MYSQL or codeIgniter 3, Any help would be very much appreciated. Im currently testing it in phpmyadmin, so an MYSQL example would be fine at this stage.
Thank you
SELECT
`id` , `title` , `image` , `category` ,
MIN( price ) AS `price` , `rrp` , `product_to_category`.`product_id` ,
LEFT( short_description, 140 ) AS description,
`measurements` , `colour_names` , `colour_values` , `brand` , `pattern`
FROM `products`
JOIN `product_to_category` ON `id` = product_to_category`.`product_id`
LEFT JOIN `product_short_descriptions` ON `product_short_descriptions`.`product_id` = `id`
WHERE `category_id` =2 AND `status` = '1' AND `price` >= '102.30' AND `price` <= '804.10'
GROUP BY `pattern` , `brand`
ORDER BY `sort_order` , `price` ASC
LIMIT 40
More Information
All data comes from the products table with the exception of the description which comes from the product_short_description table, and category_id which comes from the product_to_category table.
those two tables are joined by product_id to products.id
Here I can get the data I want, without the Joins:-
SELECT id, pattern, price
FROM products p1
WHERE price = (
SELECT MIN( price )
FROM products AS p2
WHERE p2.pattern = p1.pattern )
AND `status` =1
AND material =713
Note: material is added to substitute the category_id just for this example, as category 2 contains all products with material of 713.
The question is how do I add my 2 Joins to this and keep the results the same, as when I try and add my joins, and other Where clauses but keep the same results, as when I try it, I end up with just 2 results instead of the expected 12, so im guessing im not adding my joins correctly.
SELECT `id`, `title`, `image`, `category`, price, `rrp`, `product_to_category`.`product_id`, LEFT(short_description, 140) as description, `measurements`, `colour_names`, `colour_values`, `brand`, `pattern`
FROM `products` p1
JOIN `product_to_category` ON `id` = `product_to_category`.`product_id`
JOIN `product_short_descriptions` ON `product_short_descriptions`.`product_id` = `id`
WHERE `category_id` = 2
AND price = (
SELECT MIN( price )
FROM products AS p2
WHERE p2.pattern = p1.pattern )
AND `status` = '1'
AND `price` >= '102.30'
AND `price` <= '804.10'
GROUP BY `pattern`, `brand`
ORDER BY `sort_order`, `price` ASC
LIMIT 40
Above only shows 2 results instead of 12..
Edit: I can see now why there is only 2 results, as the inner select is not looking in category_id of 2 , So when I thought i was looking at 12 results of correct data, I was infact only looking at 2, the other ten rows were coming from outside of category 2 so when I was saying only show category 2, this is why I was only seeing two results, adding a join within this select does solve the issue, but takes a massive performance hit so is unusable.
How can the following query be rewritten for best performance? when categories can have upto 8000+ products
SELECT `id` , `title` , `image` , `category` , price, `rrp` , `product_to_category`.`product_id` , LEFT( short_description, 140 ) AS description, `measurements` , `colour_names` , `colour_values` , `brand` , `pattern`
FROM `products` p1
JOIN `product_to_category` ON `id` = `product_to_category`.`product_id`
JOIN `product_short_descriptions` ON `product_short_descriptions`.`product_id` = `id`
WHERE `category_id` =2
AND price = (
SELECT MIN( price )
FROM products AS p2
JOIN product_to_category ptc ON id = ptc.product_id
WHERE p2.pattern = p1.pattern
AND category_id =2 )
AND `status` = '1'
GROUP BY `pattern` , `brand`
ORDER BY `sort_order` , `price` ASC
LIMIT 40