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

Question

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'

    }

}

I think that you just have to use `count_users` instead of `Licensees__count_users`, this way: `'count_users' => 'count(LicenseesUsers.licensees_id)'` — arilia, Oct 09 '17 at 09:24
Hi Arilia, it is not valid that way, I already tried all the way that they are commented and does not order by that column. I also add it as virtualProperties in identity, but it does not work either. I do not know what else to do. And it must be able to **SORT** by **count_users** — Sergio, Oct 09 '17 at 17:27
It is valid, but you've made a further mistake, and you should receive an error, which you should always mention (and post including the corresponding stracktrace and your exact CakePHP version!), as it greatly helps with solving problems! — ndm, Oct 09 '17 at 18:26
Hi ndm, CakePHP 3.3.16 and does not generate error of any kind only that does not sort the data of count_users — Sergio, Oct 09 '17 at 18:36
You should definitely receive an error message once that query is being executed, as the field in the `ORDER BY` clause doesn't exist. — ndm, Oct 09 '17 at 19:18

score 1 · Answer 1 · answered Oct 09 '17 at 19:18

As mentioned in the comments, you have to use the count_users variant, ie without any aliasing, this will generally work just fine.

Things will break for you because you've made the count_users column part of your schema ($this->Licensees->schema()->addColumn()), which will cause CakePHP to treat it like a real column, and hence it will internally alias the column name, wich will cause the ORDER BY clause to use Licensees.count_users, which won't work as no such column exists (you cannot add a computed column to an existing alias).

If you need the computed column to be treated as a specific type, then modify the (select) type map instead, either by creating and paginating a proper query object, or by modifying the query on the fly in for example a finder, or via the Model.beforeFind event.

// use getSelectTypeMap() as of CakePHP 3.4
$query->selectTypeMap()->addDefaults([
    'count_users' => 'integer'
]);

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

2 Answers2