按行排序分页别名例如在CakePHP 3.x中，通过链接连接表数据

Question

正如我可以在cakephp 3.x中，我可以通过一个有计数的行来排序结果，因为我没有看到该表单在CakePHP 2.x中非常相似，没有任何问题。 缺少能够在CakePHP 3.x中做同样的事情？ 我的测试是在恩PHP 7.1 WAMP

CakePHP的v3.3.10所有的排序分页的正常工作，除了最后count_users，当我点击它没有给出一个错误，根本就没有对数据进行排序。

HTML CTP

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

控制器

$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); 

显示错误排序分页场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' 

    } 

} 

Answer 1

正如在评论中提到的，你必须使用count_users变种，即没有任何别名，这通常会工作得很好。

因为您已经创建了count_users模式的列部分（$this->Licensees->schema()->addColumn()），所以CakePHP将它视为一个真正的列，因此它将在内部别名列名，这将导致ORDER BY子句使用Licensees.count_users，因为不存在这样的列（您不能将计算列添加到现有别名），所以这不起作用。

如果您需要将计算列作为特定类型处理，请修改（选择）类型映射，或者通过创建和分页正确的查询对象，或通过修改查询，例如取景器，或通过Model.beforeFind事件。

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

又见

• API > \Cake\Database\Query::selectTypeMap()
• API > \Cake\Database\TypeMap
• Cookbook > Controllers > Pagination
• Cookbook > ORM > Retrieving Data & Results Sets > Custom Finder Methods
• Cookbook > ORM > Table Objects > Lifecycle Callbacks > beforeFind
• Cakephp-3.x: How to change the data type of a selected alias?

Answer 2

由于NDM，这是你说的话。

该问题将被注释以下几行。

控制器：

// $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);