SELECT
p.*,
pi.filename,
(SELECT min(price) FROM product_variations WHERE epos_id = p.epos_id) AS product_price,
(SELECT count(*) FROM product_variations WHERE epos_id = p.epos_id) AS variant_count
FROM products p
LEFT JOIN product_images pi ON pi.epos_id = p.epos_id
LEFT JOIN product_variations pv ON pv.epos_id = p.epos_id
WHERE p.active = 1 AND p.epos_category NOT IN ('5QCQZGSHXX3HIZDOSUYOSVIQ') AND pi.variation_id = '' AND ((p.name LIKE '%robe%'
OR CONCAT(' ', p.name, ' ') REGEXP '[[:<:]]robe(?![a-zA-Z])'
OR p.description LIKE '%robe%'
OR CONCAT(' ', p.description, ' ') REGEXP '[[:<:]]robe(?![a-zA-Z])'
OR p.epos_category = ''))
GROUP BY p.epos_id
HAVING (SELECT SUM(stock_level) FROM product_variations WHERE epos_id = p.epos_id) > 0
ORDER BY product_price DESC, p.name ASC
Filters
