Multi-Column Sorting Keeps Giving Errors
I'm trying to sort on three columns (Year DESC, Month DESC, & Day ASC) with the following code:
$Record = new Pod('bulletins');
$params = array();
$params['select'] = "*, date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%Y' ) as sortYear, date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%m' ) as sortMonth, date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%d' ) as sortDay";
$params['search'] = false;
$params['orderby'] = "sortYear DESC, sortMonth DESC, sortDay ASC";
$params['limit'] = -1;
$Record->findRecords($params);
The sort will work when I only have one 'orderby' column, otherwise I get this error:
Response: Unknown column 't.sortYear' in 'order clause'
Any ideas?
2 Answers
You can't use field aliases in ORDER BY, unless you're selecting something specifically, you shouldn't even be using the 'select' parameter.
Why not just use this as your orderby:
$params['orderby'] = "date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%Y' ) DESC, date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%m' ) DESC, date_format( str_to_date( t.bulletindate, '%Y-%m-%d' ) , '%d' ) ASC"
Thanks so much Scott!
