How to count and group by in yii2

Question

I would like to generate following query using yii2:

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:

$criteria = new CDbCriteria();
$criteria->select = 'COUNT(*) AS cnt';
$criteria->group = array('promoter_location_id', 'lead_type_id');

Thanks!

you can use create command to write your mysql query – Kshitiz Jun 25 '14 at 08:55 — Kshitiz, Jun 25 '14 at 08:55
Please post the solution as an answer. – Cthulhu Jun 26 '14 at 13:43 — Cthulhu, Jun 26 '14 at 13:43

deacs · Accepted Answer · 2014-07-25T17:30:09.740

37

Solution:

$leadsCount = Lead::find()
->select(['COUNT(*) AS cnt'])
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->all();

and add public $cnt to the model, in my case Lead.

As Kshitiz also stated, you could also just use yii\db\Query::createCommand().

edited Jul 25 '14 at 17:30

answered Jun 27 '14 at 07:12

deacs

4,259
2
27
37

2

This works and I had to add the public variable to the model class and mentioned by @deacs and replace the all() method with `->createCommand()->queryAll();` Also note that my select() method has multiples `->select(['type, date(significant_timestamp) AS significant_timestamp, COUNT(*) AS size'])` – Gajen Sunthara Sep 05 '17 at 19:18

score 9 · Answer 2 · answered May 26 '16 at 06:22

9

You can get the count by using count() in the select Query

$leadCount = Lead::find()
->where(['approved'=>'1'])
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();

Reference Link for various functions of select query

answered May 26 '16 at 06:22

Kailas

3,173
5
42
52

score 8 · Answer 3 · answered Jun 11 '15 at 02:33

If you are just interested in the count, use yii\db\Query as mentioned by others. Won't require any changes to your model:

$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

t6nnp6nn · Answer 4 · 2017-02-22T08:01:53.810

4

Without adding the $cnt property to model

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

edited Feb 22 '17 at 08:01

answered Feb 21 '17 at 11:47

t6nnp6nn

319
1
6

1

Please do not merely post code but also add a few words explaining what the pivotal difference is. – mkl Feb 21 '17 at 12:09
But we get not ActiveRecords as a result – Anton Rybalko Jun 16 '17 at 10:59
Yes. This query will not return the ActiveRecords since you define the ->select(['promoter_location_id', 'lead_type_id','COUNT(*) AS cnt']) – t6nnp6nn Jul 08 '18 at 10:20

score 0 · Answer 5 · answered Oct 25 '18 at 11:36

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:

/**
 * @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:

count($oCat->listingsApprovedActive)

Calling count on the function will return 1:

count($oCat->getListingsApprovedActive())

How to count and group by in yii2

5 Answers5

Linked