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

**URL:** <https://discourse.cakephp.org/t/problem-sort-in-pagination-join-table-field-count-in-cakephp-3-x/3322>\
**Category:** Need Help\
**Tags:** news\
**Created:** [October 9, 2017, 7:23pm UTC](https://discourse.cakephp.org/t/problem-sort-in-pagination-join-table-field-count-in-cakephp-3-x/3322 "2017-10-09T19:23:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![eom](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/eom/32/841_2.png) [@eom](https://discourse.cakephp.org/u/eom)\
**Post date:** [October 9, 2017, 7:23pm UTC](https://discourse.cakephp.org/t/problem-sort-in-pagination-join-table-field-count-in-cakephp-3-x/3322/1 "2017-10-09T19:23:47Z")

</div>

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:

> <https://stackoverflow.com/questions/46638595/sort-pagination-by-row-alias-count-for-example-in-cakephp-3-x-data-by-linking>

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'

    }

}
```

---

<div class="post-metadata">

**Author:** ![eom](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/eom/32/841_2.png) [@eom](https://discourse.cakephp.org/u/eom)\
**Post date:** [October 9, 2017, 8:55pm UTC](https://discourse.cakephp.org/t/problem-sort-in-pagination-join-table-field-count-in-cakephp-3-x/3322/2 "2017-10-09T20:55:36Z")

</div>

Solution problem

Clear set addColumn() and aliasField()

> <https://stackoverflow.com/questions/46638595/sort-pagination-by-row-alias-count-for-example-in-cakephp-3-x-data-by-linking/46653543#46653543>