Updated: 06/08/2011 3:27pm
Sometimes you need to retrieve tabledata based on a more complex sorting mechanism then just something like Name ASC. And you don't want to revert to raw SQL, but still use DataObject::get, because it'll give you all kinds of checks, caching and pagination.
Normally, if you wanted to sort a number of products based on their lowest price, you'd set the sortorder to something like this:
$sortOrder = 'Price ASC';
But suppose your record has not only a (default) Price field, but a SpecialPrice field as well. A product might not have a SpecialPrice defined for it, but if it has, it will probably be lower then the (default) Price. A possible solution to incorporate the SpecialPrice in the sortorder:
$sortorder = "CASE WHEN `CurrentPrice` AND `CurrentPrice` < `Price` " . "THEN `CurrentPrice` ELSE `Price` END ASC";
SilverString, on rebuilding the query, will add a custom field _SortColumn0 to the SELECT clause, and use it to sort the set. Internally this would look something like this:
SELECT ... , CASE WHEN `CurrentPrice` AND `CurrentPrice` < `Price THEN `CurrentPrice` ELSE `Price` END AS `_SortColumn0` FROM ... ORDER BY `_SortColumn0` ASC
This might not be the fastest query when dealing with thousands of records, but neither would implementing logic to do the sorting afterwards be! And it leaves pagination intact. And I suppose you could even use the _SortColumn0 field in your template.
Thoughts: This would be a way to sneek in a custom complex field to the resultset - but only if you want to sort by it! Can this be achieved some other way? What's a CompositDBField? Does it have anything to do with this subject?