Created
May 8, 2019 16:45
-
-
Save devig/74414ac53b5142a1507118be39f1ebe5 to your computer and use it in GitHub Desktop.
Триггеры для сортировки без нулевой цены
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; |
-- for mysql 8
ALTER TABLE products
DROP INDEX priceon_price_asc
,
DROP INDEX priceon_price_desc
,
ADD INDEX priceon_price_asc
(priceon
DESC, price
ASC),
ADD INDEX priceon_price_desc
( price
DESC, priceon
DESC);
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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
ORDER BY
priceon
DESC,price