Вывод минимальной цены из всех товаров в категории исключая ноль

[fanton123]

Добрый день. Opencart 2.1.0.2.1. Стоит задача вывести на всех категориях минимальную и максимальную цену товара. С максимальной ценой проблем нет, всё работает. Не получается вывести минимальную цену исключая нулевую цену товара.

Запрос на максимальную цену товара:

Спойлер

SELECT p.price, ps.price AS price_special FROM ".DB_PREFIX."product p LEFT JOIN ".DB_PREFIX."product_to_category AS ptc ON ptc.product_id = p.product_id LEFT JOIN ".DB_PREFIX."product_special ps ON (p.product_id=ps.product_id) WHERE ptc.category_id = ".(int)$category_id." OR ptc.category_id IN (SELECT path_id FROM ".DB_PREFIX."category_path WHERE category_id = ".(int)$category_id." AND level = 0) AND p.price > 0 AND p.quantity > 1 AND p.date_available <= NOW() ORDER BY p.price DESC LIMIT 0,1

 

А вот запрос на минимальную цену товара упорно выводит 0(нулевую цену товара):

Спойлер

SELECT p.price, ps.price AS price_special FROM ".DB_PREFIX."product p LEFT JOIN ".DB_PREFIX."product_to_category AS ptc ON ptc.product_id = p.product_id LEFT JOIN ".DB_PREFIX."product_special ps ON (p.product_id=ps.product_id) WHERE ptc.category_id = ".(int)$category_id." OR ptc.category_id IN (SELECT path_id FROM ".DB_PREFIX."category_path WHERE category_id = ".(int)$category_id." AND level = 0) AND p.price > 0 AND p.quantity > 1 AND p.date_available <= NOW() ORDER BY p.price ASC LIMIT 0,1

 

[Yesvik]

SELECT 
	MIN(IFNULL(ps.price, p.price)) AS min_price, 
	MAX(IFNULL(ps.price, p.price)) AS max_price
FROM ".DB_PREFIX."product AS p 
LEFT JOIN ".DB_PREFIX."product_to_category AS ptc USING(product_id) 
LEFT JOIN ".DB_PREFIX."product_special AS ps USING(product_id) 
WHERE IFNULL(ps.price, p.price) > 0
AND (ptc.category_id = ".(int)$category_id." 
	OR ptc.category_id IN (SELECT path_id FROM ".DB_PREFIX."category_path WHERE category_id = ".(int)$category_id." AND level = 0)) 
AND p.quantity > 1 
AND p.date_available <= NOW() 

 

Если для всех категорий с учетом подкатегорий - то одним запросом вот так

SELECT 
	path_id AS category_id,
	MIN(IFNULL(ps.price, p.price)) AS min_price, 
	MAX(IFNULL(ps.price, p.price)) AS max_price
FROM ".DB_PREFIX."product AS p 
LEFT JOIN ".DB_PREFIX."product_to_category AS ptc USING(product_id) 
LEFT JOIN ".DB_PREFIX."oc_category_path USING(category_id)
LEFT JOIN ".DB_PREFIX."product_special AS ps USING(product_id) 
WHERE IFNULL(ps.price, p.price) > 0
AND path_id IS NOT NULL
AND p.quantity > 1 
AND p.date_available <= NOW() 
GROUP BY path_id