Sort DataList by calcualated/aggregated column

Question

is it possible to sort a Datalist/ComponentSet by an calculated/virtual/aggregated col?

I have a DataList containing events having a StartDate and EndDate. Now i want to sort the list by duration of the event (shortest to longest).

In SQL i would do:

... ORDER BY DATEDIFF(EndDate, StartDate) ASC

How to do that with silverstripe ORM

$list = Event::get()->sort('????');

An additional column containing a new Field Duration and filling this field in onAfterWrite-hooks is not preferred.

Is this possible ?

Robert

Answer 1

$list = Event::get()->sort(array('DATEDIFF("EndDate", "StartDate")' => 'ASC'));

DataList::sort doesn't do any escaping, so any valid ORDER BY clause will work fine.

Answer 2

You could use it like this:

SELECT *, DATEDIFF(EndDate, StartDate) as duration FROM table WHERE conditional = 1 ORDER BY duration ASC

Answer 3

SELECT StartDate,EndDate,DATEDIFF(EndDate, StartDate) AS DiffDate FROM myTable ORDER BY DiffDate ASC

U can calculate this diff in select to show it and order, i use 'AS DiffDate' to simplify