Jump to:

3372 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Tricky sorting issue - I'm stumped

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1068 Views
  • blueskies
    Avatar
    Community Member
    42 Posts

    Tricky sorting issue - I'm stumped Link to this post

    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!

  • Willr
    Avatar
    Forum Moderator
    5462 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

    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.

  • Ben_W
    Avatar
    Community Member
    79 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

    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

  • blueskies
    Avatar
    Community Member
    42 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

    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.

  • martimiz
    Avatar
    Forum Moderator
    1035 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

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

  • blueskies
    Avatar
    Community Member
    42 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

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

  • SnowBoarder82
    Avatar
    Community Member
    52 Posts

    Re: Tricky sorting issue - I'm stumped Link to this post

    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,

    1068 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.