Sort by price dont work fine when we sort and an overriden price exist or some discount.
What I have found out, that the price shown is correct, but the orderby takes the first price it found for that product.
For example, these are the prices stored, for product with ID 3676:
virtuemart_product_price_id virtuemart_product_id virtuemart_shoppergroup_id product_price override product_override_price product_tax_id product_discount_id product_currency product_price_publish_up product_price_publish_down price_quantity_start price_quantity_end created_on created_by modified_on modified_by
59065 3676 14 5.230000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 15:55:48 0 2024-02-08 15:55:48 0
18033 3676 3 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18032 3676 11 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18030 3676 4 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18029 3676 9 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18028 3676 8 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
59064 3676 12 5.850000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 15:55:48 0 2024-02-08 15:55:48 0
18027 3676 7 6.250000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18031 3676 10 6.250000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18026 3676 6 6.440000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
18025 3676 5 6.590000 0 0.00000 0 0 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-08 13:03:48 0 2024-02-08 15:55:48 0
69726 3676 0 10.460000 0 NULL NULL NULL 47 0000-00-00 00:00:00 0000-00-00 00:00:00 0 0 2024-02-11 18:34:50 0 2024-02-11 18:34:50 0
So the price for shopper group 5 = 6.590000
However, this is the result for the ordering:
virtuemart_product_id product_price Oplopend 1
7845 0.000000
2839 2.720000
2840 2.720000
2781 3.760000
3523 4.140000
2783 4.390000
3676 5.230000
2887 6.000000
2888 6.000000
7039 6.000000
6932 6.030000
7040 6.060000
7830 6.370000
4424 6.980000
2784 7.450000
2835 8.740000
2836 8.740000
2837 8.740000
7417 9.820000
7153 10.880000
As you can see, the value is 5.230000, the value for shopper group 14. Now if I delete this price. It will take the next price, that of shopper group 3.
There is no groups in my prices. Just a simple price with override price enables to put a discount on that. NO GROUPS.
Ordering by price doesnt take in mint that a discount applied. It ordering it by the starting price of product not by the final price.