Order by price

[Xavier Vidal]

Hi

Recently i've discovered a feature that we need to change in our store. Ordering products in the list by price only orders the visible ones....

I know this is a logical thing, as each product needs to calculate the price in case there's any special price, so the list cannot be retrieved as a simple SQL order by price.

I'm trying to reimplement the core as our customers need to order by price.

Anyone did anything related to this feature?

[Xavier Vidal]

One way to do it (i'm sure there will be better ways...) is to write down a MySQL function, so instead to get the price column, we can calculate at server level the final price.

DELIMITER $$

DROP FUNCTION IF EXISTS `calculatePrice` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `calculatePrice`(
 price DECIMAL(13,6),
 reduction_price DECIMAL(10,2),
 reduction_percent FLOAT,
 reduction_from DATE,
 reduction_to DATE) RETURNS decimal(13,6)
   NO SQL
BEGIN
 DECLARE calculated_price DECIMAL(13,6);

 SET calculated_price = price;

 IF(reduction_price = 0 AND reduction_percent = 0) THEN
   RETURN price;
 END IF;

 IF(reduction_price > 0 AND reduction_from = reduction_to) THEN
   SET calculated_price = (price - reduction_price);
   RETURN calculated_price;
 END IF;

 IF(reduction_percent > 0 AND reduction_from = reduction_to) THEN
   SET calculated_price = price - (price * (reduction_percent / 100));
   RETURN calculated_price;
 END IF;

 IF(reduction_price > 0 AND reduction_from <= CURDATE() AND reduction_to >= CURDATE()) THEN
   SET calculated_price = (price - reduction_price);
   RETURN calculated_price;
 END IF;

 IF(reduction_percent > 0 AND reduction_from <= CURDATE() AND reduction_to >= CURDATE()) THEN
   SET calculated_price = price - (price * (reduction_percent / 100));
   RETURN calculated_price;
 END IF;

 RETURN calculated_price;
END $$

DELIMITER ;