Clever Geek Handbook

Sorting a list of data using a counted / aggregated column

Is it possible to sort the Datalist / ComponentSet dataset using calculated / virtual / aggregated col?

I have a DataList that contains events that have StartDate and EndDate. Now I want to sort the list by event duration (shortest to longest).

In SQL, I would do:

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

How to do it with silverstripe ORM

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

An extra column containing the new field duration and populating that field in onAfterWrite-hooks is not preferred.

Is it possible?

Robert

+3


source to share


3 answers 


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


DataList::sort

does not execute any screens, so any valid ORDER BY clause will work fine.

+6


source 


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


U can compute this diff in select to show it and order, I use `AS DiffDate 'for simplification

+1


source


You can use it like this:

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


source






More articles:


All Articles