php 如何在yii2中计数和分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24389765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count and group by in yii2
提问by deacs
I would like to generate following query using yii2:
我想使用 yii2 生成以下查询：
SELECT COUNT(*) AS cnt FROM lead WHERE approved = 1 GROUP BY promoter_location_id, lead_type_id
SELECT COUNT(*) AS cnt FROM lead WHERE approved = 1 GROUP BY promoter_location_id, lead_type_id
I have tried:
我试过了：
$leadsCount = Lead::find()
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();
Which generates this query:
生成此查询：
SELECT COUNT(*) FROM (SELECT * FROM `lead` WHERE approved = 1 GROUP BY `promoter_location_id`, `lead_type_id`) `c`
In yii 1.x I would've done the following:
在 yii 1.x 中，我会执行以下操作：
$criteria = new CDbCriteria();
$criteria->select = 'COUNT(*) AS cnt';
$criteria->group = array('promoter_location_id', 'lead_type_id');
Thanks!
谢谢！
回答by deacs
Solution:
解决方案：
$leadsCount = Lead::find()
->select(['COUNT(*) AS cnt'])
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->all();
and add public $cntto the model, in my case Lead.
并添加public $cnt到模型中，在我的情况下为 Lead。
As Kshitiz also stated, you could also just use yii\db\Query::createCommand().
正如 Kshitiz 所说，你也可以只使用yii\db\Query::createCommand().
回答by Kailas
You can get the count by using count()in the select Query
您可以通过在选择查询中使用count()来获取计数
$leadCount = Lead::find()
->where(['approved'=>'1'])
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();
Reference Linkfor various functions of select query
select查询的各种功能参考链接
回答by Harman Dhillon
If you are just interested in the count, use yii\db\Queryas mentioned by others. Won't require any changes to your model:
如果您只是对计数感兴趣，请使用yii\db\Query其他人提到的。不需要对您的模型进行任何更改：
$leadsCount = (new yii\db\Query())
->from('lead')
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();
Here's a link to the Yii2 API documentation
这是Yii2 API 文档的链接
回答by t6nnp6nn
Without adding the $cntproperty to model
不将$cnt属性添加到模型
$leadsCount = Lead::find()
->select(['promoter_location_id', 'lead_type_id','COUNT(*) AS cnt'])
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->createCommand()->queryAll();
回答by Rich Harding
Just a note, in case it helps anyone, that a getter used as a property is countable (whereas if called as a function it will return 1). In this example, I have a Category class with Listings joined by listing_to_category. To get Active, Approved Listings for the Category, I return an ActiveQuery, thus:
请注意，如果它对任何人有帮助，用作属性的 getter 是可数的（而如果作为函数调用，它将返回 1）。在这个例子中，我有一个类别类，其中的列表由listing_to_category 连接。为了获得该类别的有效、批准的列表，我返回一个 ActiveQuery，因此：
/**
* @return \yii\db\ActiveQuery
*/
public function getListingsApprovedActive() {
return $this->hasMany(Listing::className(), ['listing_id' => 'listing_id'])
->viaTable('listing_to_category', ['category_id' => 'category_id'])
->andWhere(['active' => 1])->andWhere(['approved' => 1]);
}
Calling count on the property of the Category will return the record count:
对 Category 的属性调用 count 将返回记录计数：
count($oCat->listingsApprovedActive)
Calling count on the function will return 1:
对函数调用 count 将返回 1：
count($oCat->getListingsApprovedActive())
