I want to create a pagination sort link on a virtual field/entity property in CakePHP 3.0.
In CakePHP 2.x I used to create a virtual field, and then create a pagination sort link on that field. However, in CakePHP 3.0, virtual fields have been replaced by virtual entity properties.
Is there any way I can get this working in CakePHP 3.0?
In my situation, I have a first_name and last_name column, which are combined as full_name in a virtual entity property. I want to sort on the full_name.
As stated in the linked docs, virtual properties cannot be used in finds. That's by design, virtual properties only live in entities, they are built in PHP after data has been retrieved from the database.
So let's forget about virtual properties for a moment, and concentrate on queries and computed columns.
sortWhitelist
Just like columns of associated models, computed columns need to be specified in the sortWhitelist
option in order to be useable for sorting!
Cookbook > Controllers > Components > Pagination > Control which Fields Used for Ordering
You have some options here, for example you could define computed columns in the pagination options:
$this->paginate = [
// ...
'sortWhitelist' => [
'id',
'first_name',
'last_name',
'full_name',
// ...
],
'fields' => [
'id',
'first_name',
'last_name',
'full_name' => $this->Table->query()->func()->concat([
'first_name' => 'literal',
'last_name' => 'literal'
]),
// ...
],
'order' => [
'full_name' => 'DESC'
]
];
Another, more reusable option would be to use a custom finder:
$this->paginate = [
// ...
'sortWhitelist' => [
'id',
'first_name',
'last_name',
'full_name',
// ...
],
'finder' => 'withFullName',
'order' => [
'full_name' => 'DESC'
]
];
public function findWithFullName(\Cake\ORM\Query $query, array $options)
{
return $query->select([
'id',
'first_name',
'last_name',
'full_name' => $query->func()->concat([
'first_name' => 'literal',
'last_name' => 'literal'
]),
// ...
]);
}
It's also possible to directly pass query objects to Controller::paginate()
:
$this->paginate = [
// ...
'sortWhitelist' => [
'id',
'first_name',
'last_name',
'full_name',
// ...
],
'order' => [
'full_name' => 'DESC'
]
];
$query = $this->Table
->find()
->select(function (\Cake\ORM\Query $query) {
return [
'id',
'first_name',
'last_name',
'full_name' => $query->func()->concat([
'first_name' => 'literal',
'last_name' => 'literal'
]),
// ...
];
});
$results = $this->paginate($query);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With