Pagination sort link on a virtual field/entity property in CakePHP 3.0

Question

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.

Soothe

Answer 1

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.

Computed columns need to be specified via 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

Via pagination options

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'
    ]
];

A custom finder

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'
        ]),
        // ...
    ]);
}

• Cookbook > Controllers > Components > Pagination > Using Controller::paginate()
• Cookbook > ... ORM > Retrieving Data & Results Sets > Custom Finder Methods

Separate custom query

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);

Petulah

I will take a look into this (probably tomorrow). I'll let you know if it worked. Thanks in advance. – Soothe

Answer 2

Set your default sort order to be the same as your virtual field:

public $paginate = [
  'order' => [
    'first_name' => 'ASC',
    'last_name' => 'ASC',
  ]
];

Then just add the following to your View to prevent the paginator from overriding the default order unless specified by the user:

if (empty($_GET['direction'])) { $this->Paginator->options(['url' => ['direction' => null, 'sort' => null]]); }

Brython