在CakePHP 3中如何将COUNT(*)与find('list')一起使用? [英] How do you use COUNT(*) with find('list') in CakePHP 3?

问题描述

在CakePHP 3中，我有一个名为Articles的模型和一个名为'subject'的字段，并且遇到了一个障碍，试图检索100个最常用的文章主题的列表.

In CakePHP 3, I had a model called Articles and a field called 'subject', and I ran into a roadblock trying to retrieve a list of the 100 most commonly-used article subjects.

以下代码的结果SQL从 all 个可能的字段中选择了 not COUNT(*):

The following code's resulting SQL selected all of the possible fields and not COUNT(*):

$articles->find('list', [
    'keyField' => 'subject',
    'valueField' => 'COUNT(*)'
])
->group('subject')
->order(['COUNT(*)' => 'DESC'])
->limit(100)
->toArray();

然后，我想起了"CakePHP的ORM提供了抽象某些常用的SQL函数." .但是以下代码导致错误:函数名称必须是字符串":

Then I remembered "CakePHP’s ORM offers abstraction for some commonly used SQL functions.". But the following code resulted in "Error: Function name must be a string":

$countFunc = $this->find()->func()->count('*');
$articles->find('list', [
    'keyField' => 'subject',
    'valueField' => $countFunc
])
->group('subject')
->order([$countFunc => 'DESC'])
->limit(100)
->toArray();

推荐答案

幸运的是，José向我提示了这个技巧，它就像一种魅力:

Fortunately, José tipped me off to this trick, which works like a charm:

$articles->find('list', [
    'keyField' => 'subject',
    'valueField' => 'count'
])
->select([
    'subject',
    'count' => $this->find()->func()->count('*')
])
->group('subject')
->order(['count' => 'DESC'])
->limit(100)
->toArray();

这篇关于在CakePHP 3中如何将COUNT(*)与find('list')一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！