Issue: I am writing a query that involves a count() operation on a column but returns the count in the string format. As a result the sorting on the front end is messed up.
Details: I am writing the following query in Zend:
$select = $this->select()
->setIntegrityCheck(false)
->from(array('s' => 'student'),
array('s.id',
'start_date' => new Zend_Db_Expr("date(s.start_date)"),
))
->joinLeft(array('ps' => 'pstudent'),
's.id = ps.st_id',
array('pscount' => new Zend_Db_Expr('count(ps.st_id)') ))
->where('ps.status = "phd"');
$result = $this->getAdapter()->fetchAll($select);
This query returns me the pscount
by using the Zend_Db_Expr (count(ps.st_id))
. At a later point in code I encode this data to json format and return to the front end. Since the data from db comes as a string (ie pscount
is a string), when I sort on the front end on pscount
column of the grid, I get the following sequence.
1
10
100
11
12
13
However,I want it to sort by numeric value so the output is:
1
10
11
12
13
100
I tried writing the Zend_Db_Expr
as new Zend_Db_Expr('cast(count(ps.study_id) as signed)')
but there is no change in the results. I cannot use an order by in the query as well due to application requirements. The only other option is if I traverse through the $result
and then do a (int)
type conversion in php code. However that is the last option. I would prefer to do this in mysql query so that I do not have to traverse through the results unnecessarily.
Any help is really appreciated.
Just get it as an array of strings and use sort($someArray, SORT_NUMERIC)
. This will sort them as if they were numbers.
If you need to sort complex objects, use usort() with a comparison function.
I don't believe you can order results inside mysql without using order by
.