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)?

Any help would be appreciated!

I had to do a little bit of magic to get the live vs stage checking as well for forum if you want to go the sql route. https://github.com/silverstripe/silverstripe-forum/blob/master/code/ForumHolder.php#L302

Or you could sort via the dataobjectset ORM but that could be horribly slow.

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.

$page_query = "SELECT list.* FROM Listing list, SiteTree_Live st WHERE (list.CategoryID=$this->ID OR (st.ClassName='CategoryPage' AND st.ParentID=$this->ID) ) AND list.CategoryID=st.ID AND list.Active='1' ORDER BY list.BusinessName";
if(DB::query($page_query)->valid()){
        $records = DB::query($page_query);
        $myData = singleton('Listing')->buildDataObjectSet($records);
} else {
        $myData = false;
}

return $myData;

Here is a link for raw sql query:
http://doc.silverstripe.org/sapphire/en/reference/sqlquery

Thanks guys! I think I will have to go the basic SQLQuery route. And I appreciate your stage/live check function, Willr, I think that will make the sql route a definite possibility now, thanks.

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):

  
$lowestPrice =
	 "case when `CurrentPrice` and "
	."`CurrentPrice` < `Price` and not "
	."`DisableCurrentPrice` and "
	."(`StartDate` is null or CURDATE() >= `StartDate`) and "
	."(`EndDate` is null or CURDATE() <= `EndDate`) "
	."then `CurrentPrice` else `Price` end";
);

$sortOrder = "$lowestPrice ASC";

$products = DataObject::get('Product', '', $sortOrder);

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!!

$Sort =
   "CASE ".
   "WHEN `SpecialPrice` > 0 THEN `SpecialPrice` ".
   "ELSE `Price`".
   "END";
...
$DataObjectSet = DataObject::get("ProductPage", $Where, $Sort." ".$SortDirection, $Join, $Limit);

Hey Guys,

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)

function ProductsShowable($extraFilter = '', $recursive = true){
$filter = ""; //
$join = "";

$this->extend('updateFilter',$extraFilter);

if($extraFilter) $filter.= " AND $extraFilter";

//added =0 to show 0/poa products 01062012
if(self::$must_have_price) $filter .= " AND \"Price\" >= 0";

$limit = (isset($_GET['start']) && (int)$_GET['start'] > 0) ? (int)$_GET['start'].",".self::$page_length : "0,".self::$page_length;
$sort = (isset($_GET['sortby'])) ? Convert::raw2sql($_GET['sortby']) : "\"FeaturedProduct\" DESC,\"URLSegment\"";

//hard coded sort configuration //TODO: make these custom
if($sort == "NumberSold") $sort .= " DESC";

$groupids = array($this->ID);

if(($recursive === true || $recursive === 'true') && self::$include_child_groups && $childgroups = $this->ChildGroups(true))
$groupids = array_merge($groupids,$childgroups->map('ID','ID'));

$groupidsimpl = implode(',',$groupids);

$join = $this->getManyManyJoin('Products','Product');
$multicatfilter = $this->getManyManyFilter('Products','Product');

//TODO: get products that appear in child groups (make this optional)

$products = DataObject::get('Product',"(\"ParentID\" IN ($groupidsimpl) OR $multicatfilter) $filter",$sort,$join,$limit);

$allproducts = DataObject::get('Product',"\"ParentID\" IN ($groupidsimpl) $filter","",$join);

if($allproducts) $products->TotalCount = $allproducts->Count(); //add total count to returned data for 'showing x to y of z products'
if($products && $products instanceof DataObjectSet) $products->removeDuplicates();
return $products;
}

I'd definitely appreciate any help on this.

Thank you,