Author Topic: Ordering by price with price override - research  (Read 855 times)


  • 3rd party VirtueMart Developer
  • Beginner
  • *
  • Posts: 27
Ordering by price with price override - research
« on: February 16, 2017, 21:42:12 pm »
My client wants to show products by price ordering. In category i have products with or without discount. Discount done via price override (pic 0002). But with override there are ordering problem, see pic 0001. Ive done some research: in ordering all products you take price and substract TAX (or some other rule attached to price). From override price you dont substract anything, but this price includes TAX too.

In ordering VM uses query:
SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` , IF(pp.override, pp.product_override_price, pp.product_price) as product_price FROM `#__virtuemart_products` as p LEFT JOIN `#__virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_categories` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `#__virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` WHERE ( `pc`.`virtuemart_category_id` = 3 AND `c`.`published` = 1 AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY `product_price` ASC, `virtuemart_product_id` ASC

With this query i see data (pic 0002). There i see first and secont product have price without TAX but third product has override price (what is includes TAX).

Now question: is it bug or i use price override wrong? This post have same problem:
VirtueMart Blue Corvus 9424