Tricky sorting issue - I'm stumped

So I have a "ProductPage", which extends from Page. It has, among others, two db fields related to price: Price and SpecialPrice. SpecialPrice is filled in if the item is on sale, if not it's just 0.

Now I need to sort a dataobjectset (retrieved through DataObject::get) of ProductPages on price. Which would mean that if a product's SpecialPrice is greater than 0, we'd need to use that price, otherwise use Price. The set needs to be sorted on the lowest price available of the product (sometimes Price, sometimes SpecialPrice.

In mysql I'd use SELECT greatest(Price, SpecialPrice) as LowestPrice..... ORDER BY LowestPrice ASC

But I can't change the select statement of DataObject::get. So then I thought I'd use SQLQuery, but I can't get it to switch between the ProductPage and ProductPage_Live table automagically (which ::get does for you).

Does anyone have any alternative suggestions? Or know how to change the select on the DataObject::get? Or know how to get SQLQuery to switch between ProductPage and ProductPage_Live (I've already tried using the ticks)?

option 1.
you may try add LowestPrice into ProductPage, but do not create any text field in your cms, so now in your database table, you have this extra field. Then add you calculation sequnce in onBeforeWrite(), ie work out which is the lowest value and then store it in the LowestPrice field. but you need to work out if user click 'save' or 'Save and publish'. From then on you just do your query as normal since you got the LowestPrice.

Option 2.
do a RAW sql query, and cast the result into silverstripe dataojectset, be warned, it may lose some build in functionality, but in your case, I think it's worth a shot. one of my query goes like this.

This could easily be tackled by adding a 'select case when else...' in to the select clause. Unfortunately DataObject::get() doesn't let you do that. There is a weird solution to this problem, that I stumbled upon trying to solve the same issue. Just add the 'case when' bit to the sortorder: SilverStripe will spot it, move it to the select clause, give it a temporary name ('_SortColumn0') and then sort on it, wouldn't you believe it :-)

Here's a copy from my script that takes into account startdate and enddate for a special offer and whether it's been taken ofline (just adapt the query):

martimiz, thank you!!!! That was exactly what I was looking for. I was able to sort on the lesser of two columns (Price and SpecialPrice) by using a simple case/when. So when there is a SpecialPrice, it returns that number for the sort, and else just the Price. It works a charm!!

I have a slight twist on a similar sorting issue. The case is in SS2.4.1, with a custom modified shop 0.8.3.

I have a standard product category page where all products for that category are displayed. However, when a product price is listed as 0 the product is still listed and displays as "POA" (for Pricing on Application).

For sorting by price any product of POA is currently listed first (makes sense as the price is 0), however I need any POA product to display at the end of the results after products with actual pricing, instead of the start. Is there anyway I can specify in the sorting code for products with a pricing of 0 to be listed last in the set...

I believe the magic for this currently happens in Productgroup.php within the Productshowable function (shown below)