Problem sort in Pagination join table field count in CakePHP 3.x

news

#1

Hello folks, I have a problem with CakePHP 3.x. which I can not solve.
I want to sort the data of a column that I create by a join of tables and I generate a count (). It works everything, but when I want to sort that column I can not, it gives me error.

More error information:

As I can in cakephp 3.x I can sort the results by a row that has a count, because I do not see the form, something very similar in CakePHP 2.x works without problem. What is missing to be able to do the same in CakePHP 3.x …? My Test is en CakePHP v3.3.10 in PHP 7.1 WAMP

All the sort of pagination works correctly except the final count_users, when I click it does not give an error, simply do not sort the data.

HTML ctp

<?= $this->Paginator->sort('count_users',_('N° Users')); ?>

Controller

$this->Licensees->schema()
    ->addColumn('count_users', [
        'type' => 'integer',
    ]);
$this->Licensees->aliasField('count_users');

$where = [
    'recursive'=>-1,
    'fields' => [
       'Licensees.id',
       'Licensees.name',
       'Licensees.created',
       'Licensees.modified',
       'Licensees__count_users' => 'count(LicenseesUsers.licensees_id)',
       // 'count_users' => 'count(LicenseesUsers.licensees_id)', 
       // 'Licensees.count_users' => 'count(LicenseesUsers.licensees_id)', 
     ],
    'sortWhitelist' => ['name','count_users','created','modified'],
    'join' => [
        'LicenseesUsers' => [
            'table' => 'licensees_users',
            'type' => 'LEFT',
            'conditions' => [
                'LicenseesUsers.licensees_id = Licensees.id'
            ],
        ],
    ],
    'group' => 'Licensees.id'
];

 // Set pagination
$this->paginate = $where;

// Get data
$licensees = $this->paginate($this->Licensees);

Show error Sort pagination field count_users

object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Licensees.id AS `Licensees__id`, Licensees.name AS `Licensees__name`, Licensees.created AS `Licensees__created`, Licensees.modified AS `Licensees__modified`, count(LicenseesUsers.licensees_id) AS `Licensees__count_users`, count(LicenseesUsers.licensees_id) AS `count_users` FROM licensees Licensees LEFT JOIN licensees_users LicenseesUsers ON LicenseesUsers.licensees_id = Licensees.id LEFT JOIN users Users ON Users.id = LicenseesUsers.users_id GROUP BY Licensees.id  ORDER BY Licensees.count_users asc LIMIT 50 OFFSET 0',
    'params' => [],
    'defaultTypes' => [
        'Licensees__id' => 'uuid',
        'Licensees.id' => 'uuid',
        'id' => 'uuid',
        'Licensees__name' => 'string',
        'Licensees.name' => 'string',
        'name' => 'string',
        'Licensees__active' => 'boolean',
        'Licensees.active' => 'boolean',
        'active' => 'boolean',
        'Licensees__deleted_at' => 'datetime',
        'Licensees.deleted_at' => 'datetime',
        'deleted_at' => 'datetime',
        'Licensees__created' => 'datetime',
        'Licensees.created' => 'datetime',
        'created' => 'datetime',
        'Licensees__modified' => 'datetime',
        'Licensees.modified' => 'datetime',
        'modified' => 'datetime',
        'Licensees__count_users' => 'integer',
        'Licensees.count_users' => 'integer',
        'count_users' => 'integer'
    ],
    'decorators' => (int) 0,
    'executed' => false,
    'hydrate' => true,
    'buffered' => true,
    'formatters' => (int) 0,
    'mapReducers' => (int) 0,
    'contain' => [],
    'matching' => [],
    'extraOptions' => [
        'recursive' => (int) -1,
        'scope' => null,
        'sortWhitelist' => [
            (int) 0 => 'name',
            (int) 1 => 'count_users',
            (int) 2 => 'created',
            (int) 3 => 'modified'
        ],
        'whitelist' => [
            (int) 0 => 'limit',
            (int) 1 => 'sort',
            (int) 2 => 'page',
            (int) 3 => 'direction'
        ]
    ],
    'repository' => object(App\Model\Table\LicenseesTable) {

        'registryAlias' => 'Licensees',
        'table' => 'licensees',
        'alias' => 'Licensees',
        'entityClass' => 'App\Model\Entity\Licensee',
        'associations' => [
            [maximum depth reached]
        ],
        'behaviors' => [
            [maximum depth reached]
        ],
        'defaultConnection' => 'default',
        'connectionName' => 'default'

    }

}

#2

Solution problem

Clear set addColumn() and aliasField()