MYSQL - Pricing filter for products with and without child rows

Question

Ok, so I have a single table that stores all products. There are three types of products; "simple", "configurable" and "variant".

Simple and Variant products are just a single row with a "price" column and an optional "sale_price" column.

Configurable products have a zero value for their "price" and "sale_price" columns but instead have child (variant) product rows (joined by a "parent_id" column).

What I'm trying to do is create a filter based on price. If a product has a "sale_price" value it should use that instead of the "price" value. This works for simple products:

select `products`.*, 
`lookup_product_categories`.`category_id` as `pivot_category_id`, 
`lookup_product_categories`.`product_id` as `pivot_product_id`
from `products` 
inner join `lookup_product_categories` on `products`.`id` = `lookup_product_categories`.`product_id` 
where `lookup_product_categories`.`category_id` = '38' 
and (
    `type` = 'simple' 
    and 
    IF (
        `sale_price` > 0,
        `sale_price` >= 30 AND `sale_price` <= 150,
        `price` >= 30 AND `price` <= 150
    ) 
) 
and `active` = '1' 
and `valid` = '1' 
and `products`.`deleted_at` is null 
order by `created_at` desc

What I need to do now is in the case of "configurable" products get the child products, determine if each should use the sale or normal price and then return the parent configurable product if at least one of the child products fall within the filter range, in this case; between 30.00 and 150.00.

Thanks in advance. I'm totally stuck on this. I've tried inner joins but I can't quite get the syntax right.

Answer 1

I assumed that you have following columns in a table

id | type | sale_price | price | parent_id

without sample data it's hard to write the query, but you could try below:

select * from my_table t1
where (type in ('simple', 'variant') and sale_price is not null and sale_price between 30 and 150)
   or (type in ('simple', 'variant') and sale_price is null and price between 30 and 150)
   or (type = 'configurable' and exists(
        select 1 from my_table
        where (t1.parentid = id and sale_price is not null and sale_price between 30 and 150)
           or (t1.parentid = id and sale_price is null and price between 30 and 150)
      ));