How can I sort by price when some items have Tax added

[Psytanium]

Hello,

I have a problem sorting products by price when in the same category some products have added Tax and other are Tax free.

Example :

• Product A price is 100$ with 10% Tax added = 110$
• Product B price is 105$ without tax

When sorted by price Ascending, product A is placed after product B, because 100 < 105, the price value in "$listing_sql" query is net without adding Tax.

How can I fix this sorting issue ?

[MrPhil]

That's going to be complicated. I understand that sorting is done during the SQL query, which is without tax, and then any tax is added on in a separate step. Someone might write code to take a large array of product information, after tax has been added, and re-sort it to fix the order. It's ugly, but unless you can find a way to have the tax added to the price during the SQL query (before sort), that's the only way that I know of doing it.

I take it you're in a country that requires that prices be displayed with tax (where applicable), rather than allowing prices without tax?

[Psytanium]

1 hour ago, MrPhil said:

That's going to be complicated. I understand that sorting is done during the SQL query, which is without tax, and then any tax is added on in a separate step. Someone might write code to take a large array of product information, after tax has been added, and re-sort it to fix the order. It's ugly, but unless you can find a way to have the tax added to the price during the SQL query (before sort), that's the only way that I know of doing it.

I take it you're in a country that requires that prices be displayed with tax (where applicable), rather than allowing prices without tax?

Exactly, I have to display prices with Tax

Do you think there is a way to modify the query, in a way like: if(p.products_class_tax_id, multiply price, products_price) AS final_price ?

I think this topic is discussed somewhere in this forums, Os Commerce is 1 of the oldest eCommerce platforms, I'm sure many people tried to sort products price and found this bug.

[MrPhil]

Is there any time you would not need the without-tax price? You could just price it "with tax" and be done with it, I suppose. Or, add a new database column for "with tax" price, and use that always or on a selector of some sort (modified query)? Are tax rates going to vary by customer?

[Dan Cole]

9 hours ago, Psytanium said:

Do you think there is a way to modify the query, in a way like: if(p.products_class_tax_id, multiply price, products_price) AS final_price ?

I don't have time to play around with this at the moment but that should be possible.  If you're having trouble with the query have a look at the product query used to handle 'specials'. That  should give you a place to start.

Dan