Sorting data returned from a database query
You're probably thinking
"Err... idiot... just use the ORDER BY functionality in the model!". Yeah, we could do that, but that's not much fun!
Here is my freshly baked People controller, which gracefully passes some data to my view. Nothing special here.
<?php
class PeopleController extends AppController {
var $name = 'People';
function index() {
$this->set('people', $this->Person->find('all'));
}
}
?>
The
$people array is in the standard format we receive from our model, and has a belongsTo association (Occupation).
Array
(
[0] => Array
(
[Person] => Array
(
[id] => 1
[occupation_id] => 2
[name] => Harry Potter
[birth_date] => 1980-07-31
)
[Occupation] => Array
(
[id] => 2
[name] => Student
)
)
[1] => Array
(
[Person] => Array
(
[id] => 2
[occupation_id] => 1
[name] => Albus Dumbledore
[birth_date] => 1881-08-01
)
[Occupation] => Array
(
[id] => 1
[name] => Headmaster
)
)
[2] => Array
(
[Person] => Array
(
[id] => 3
[occupation_id] => 3
[name] => Severus Snape
[birth_date] => 1959-01-09
)
[Occupation] => Array
(
[id] => 3
[name] => Professor
)
)
)
Default order
Instead of dumping the entire contents of the
$people array after each Set::sort() call, I will present the data in a pretty table.
Person.id |
Person.name |
Person.birth_date |
Occupation.id |
Occupation.name |
1 |
Harry Potter |
1980-07-31 |
2 |
Student |
2 |
Albus Dumbledore |
1881-08-01 |
1 |
Headmaster |
3 |
Severus Snape |
1959-01-09 |
3 |
Professor |
Person.birth_date DESC
Set::sort() currently takes 3 arguments - the array to sort, the array value to sort on, and the sort order. As with the other
Set methods, we specify the value to sort on using a
key path. This makes it super easy to deal with complex arrays.
$people = Set::sort($people, '{n}.Person.birth_date', 'desc');
Person.id |
Person.name |
Person.birth_date |
Occupation.id |
Occupation.name |
1 |
Harry Potter |
1980-07-31 |
2 |
Student |
3 |
Severus Snape |
1959-01-09 |
3 |
Professor |
2 |
Albus Dumbledore |
1881-08-01 |
1 |
Headmaster |
Occupation.name ASC
It's just as simple sorting on a value of an associated model, in this case the person's occupation. Unfortunately the sort method doesn't current have a default sort order, so we need to specify this. We can also use the PHP constants, SORT_ASC and SORT_DESC.
$people = Set::sort($people, '{n}.Occupation.name', SORT_ASC);
Person.id |
Person.name |
Person.birth_date |
Occupation.id |
Occupation.name |
2 |
Albus Dumbledore |
1881-08-01 |
1 |
Headmaster |
3 |
Severus Snape |
1959-01-09 |
3 |
Professor |
1 |
Harry Potter |
1980-07-31 |
2 |
Student |