3070 Posts in 869 Topics by 651 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 825 Views |
-
Tricky sorting issue - I'm stumped
12 May 2011 at 8:46am Last edited: 12 May 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!
-
Re: Tricky sorting issue - I'm stumped
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.
-
Re: Tricky sorting issue - I'm stumped
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 -
Re: Tricky sorting issue - I'm stumped
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.
-
Re: Tricky sorting issue - I'm stumped
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);
-
Re: Tricky sorting issue - I'm stumped
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);
| 825 Views | ||
|
Page:
1
|
Go to Top |
