Need to Get Product ID of row relating to MIN(price)

Question

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

Answer 1

Making guesses at your table structure (and as such untested), something like this:-

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
(
    SELECT  brand ,  
            pattern ,
            SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY price), ',', 1) AS first_product_id
    FROM  products 
    WHERE  status =  '1' 
    AND  price >=  '102.30' 
    AND  price <=  '804.10' 
    GROUP BY  pattern,  
            brand
) sub0
INNER JOIN  products ON sub0.brand = products.brand AND sub0.pattern = products.pattern AND sub0.first_product_id = products.product_id
INNER JOIN  product_to_category ON  products.id =  product_to_category.product_id 
LEFT OUTER JOIN  product_short_descriptions ON  product_short_descriptions.product_id =  products.id 
WHERE  product_to_category.category_id = 2
ORDER BY  sort_order ,  
            price ASC  
LIMIT 40

This is using a sub query to find the id of the cheapest product in each brand / pattern. It does that using GROUP_CONCAT to get all the ids in price order, and then uses SUBSTRING_INDEX to get the first one (ie cheapest). This is then joined back against the other tables to get all the other fields.

This previous answer might help you understand how aggregate functions and GROUP BY work, and why your original query does not bring back the results you require:-

MYSQL - Order By Id In DESC Order, Group By X