Sort from view with when using hasOne associations

Need Help
1

I would like to sort data within a view table, the data comes from four different tables that are associated with hasOne relationship.

EER used as example:

13

The action code:

 public function index()
    {
        $this->paginate = [
            'limit' => 15,
            'contain' => [
                'Addresses',
                'Emails',
                'Phones'
            ],
            'sortWhitelist' => [
                'id',
                'name',
                'Addresses.street',
                'Emails.address',
                'Phones.number'
            ]
        ];

        $this->set('users', $this->paginate('Users'));
        $this->set('_serialize', ['users']);
    }

The relationships as follow:

$this->hasOne('Addresses')
    ->setForeignKey('user_id')
    ->setStrategy('select')
    ->setDependent(true);

$this->hasOne('Emails')
    ->setForeignKey('user_id')
    ->setStrategy('select')
    ->setDependent(true);

$this->hasOne('Phones')
    ->setForeignKey('user_id')
    ->setStrategy('select')
    ->setDependent(true);

This is my sort link:

<?= $this->Paginator->sort('Emails.address', h('E-Mail')) ?>

Problem: Can’t sort any association fields…

2

When I want to sort by related model, I have to add the fields in a whitelist, explained in Control which Fields Used for Ordering

In your example you should have

public $paginate = [
    'sortWhitelist' => [
        'id',
        // other fields
        'Emails.address',
    ]
];
3

Yes I agree, however I have a sortWhitelist array with the related models included…
It’s my first block of code in the above example.

'sortWhitelist' => [
                'id',
                'name',
                'Addresses.street',
                'Emails.address',
                'Phones.number'
            ]
4

Is there something new about the solution of this problem? I would be interested in it, too.