Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Tricky sorting issue - I'm stumped


Reply


7 Posts   1365 Views

Avatar
blueskies

Community Member, 42 Posts

12 May 2011 at 8:46am

Edited: 12/05/2011 8:47am

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!

Avatar
Willr

Forum Moderator, 5513 Posts

13 May 2011 at 4:32pm

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.

Avatar
Ben_W

Community Member, 80 Posts

13 May 2011 at 4:46pm

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

Avatar
blueskies

Community Member, 42 Posts

13 May 2011 at 11:34pm

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.

Avatar
martimiz

Forum Moderator, 1132 Posts

15 May 2011 at 8:57am

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

Avatar
blueskies

Community Member, 42 Posts

16 May 2011 at 9:15am

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

Avatar
SnowBoarder82

Community Member, 55 Posts

24 March 2014 at 3:11pm

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,