[FIXED] Sort Pagination by, row Alias Count for example in CakePHP 3.x, data by linking a join table

Issue

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'

    }

}

Solution

Thanks ndm, it was what you said.

The problem will be commented the following lines.

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

Answered By - Sergio