Written circa 2019 published from Drafts April '25
I wanted to get the last 16 records in a table but I want the values returned ascending
In CakePHP 2 you could do a find and sort descending and then array_reverse the result. But with CakePHP it returns an entity and therefore you can't do an array_reverse.
However the new ORM has the ability to create a query that is virtually SQL in PHP so here is how I got around it:
public function viewHistory($part_id = null, $assistant = false) {
// this is how we decide to take
// the data from the person_id or assistant_id columns
if ((int)$assistant === 1) {
$person_assistant = 'assistant_id';
$select_params = [
'Assistants.firstname' => 'literal',
' ',
'Assistants.lastname' => 'literal',
' — ',
'Meetings.date' => 'literal'
];
} else {
$person_assistant = 'person_id';
$select_params = [
'People.firstname' => 'literal',
' ',
'People.lastname' => 'literal',
' — ',
'Meetings.date' => 'literal'
];
}
$query = $this->Assigned->find('all');
$query = $query
->select(['fullname' => $query->func()->concat($select_params)])
->contain(['Parts', 'Meetings', 'People', 'Assistants']);
$assigned = $query
->where([ 'part_id' => $part_id ])
->where([ 'Assigned.' . $person_assistant . ' IS NOT' => null ])
->order(['Meetings.date' => 'DESC']) // sort newest to oldest
->limit(16) // take the first 16 records
->order(['Meetings.date' => 'ASC']); // then reverse the sort
$this->set('assigned', $assigned);
$this->set('_serialize', ['assigned']);
}
0 Comments