Триггеры для сортировки без нулевой цены

change_price_triggers.sql

DROP TRIGGER `products_update_row`;
DROP TRIGGER `products_insert_row`;
DELIMITER ;;
CREATE TRIGGER `products_update_row` BEFORE UPDATE ON `products` FOR EACH ROW
IF NEW.price=0 THEN 
 
    SET NEW.`priceon` = 0;
ELSE
    SET NEW.`priceon` = 1;

END IF;;

CREATE TRIGGER `products_insert_row` BEFORE INSERT ON `products` FOR EACH ROW
IF NEW.price=0 THEN 
 
    SET NEW.`priceon` = 0;
ELSE
    SET NEW.`priceon` = 1;

END IF;;
DELIMITER ;

SELECT SQL_NO_CACHE p.* , cp.category_id
FROM products p left join category_product cp on (p.id=cp.product_id and cp.category_id=1) WHERE cp.category_id=1
ORDER BY priceon DESC, price ASC
LIMIT 30;
SELECT SQL_NO_CACHE p.*
FROM products p WHERE MATCH (category_id) AGAINST ('1111')
ORDER BY priceon DESC, price ASC
LIMIT 30