Yii2: How create statistical query in relation?

[incrize]

How create statistical query in relation?
In Yii1:

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),
            'categoryCount'=>array(
                self::STAT, 'Category', 'post_category(post_id, category_id)'
            ),
        );
    }
}

[samdark]

class Customer extends \yii\db\ActiveRecord
{
    public function getOrderCount()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])->count();
    }
}

$orderCount = Customer::find(['id' => 10])->getOrderCount();

[incrize]

Да я понимаю, что так можно.
Но это для одного элемента, я бы хотел использовать такую связь для всего списка элементов, чтобы избежать запросов в цикле.

Сейчас если написать так:

$customers = Customer::find()->with('orders')->all();

Получим следующие sql запросы:

SELECT * FROM tbl_customer LIMIT 100;
SELECT * FROM tbl_orders WHERE customer_id IN (1,2,...)

Хотелось бы что то подобное.

Пока смотрел возможности родился такой вариант:

public function getClientCount()
{
    $relation = $this->hasOne(Client::className(), ['insurance_id' => 'id']);
    $relation->select = "COUNT(*) as `count`, `insurance_id`";
    $relation->groupBy(['insurance_id']);
    $relation->asArray();

    return $relation;
}

Но как то не очень выглядит и в сlientCount получаем массив, а не само значение.

[Ragazzo]

bump, @qiangxue what your thoughts about this one?

[cebe]

So this is basically about eager loading for statistical queries?

[Ragazzo]

yes

[cebe]

set for RC, will think about it.

[Ragazzo]

this one goes up. is it time to implement it? I see that it is scheduled to RC, but we already in RC stage so the faster it will be implemented the better )

[cebe]

@Ragazzo any suggestions? Just saying to hurry does not help anything to make progress here ;)

[Ragazzo]

naah, was not looking for internals of AR, maybe if i will have time, but not sure about it ) Sorry if offended you, was not upping this issue for offense, only for notice that this is a very useful enh and is at the bottom of all issues , because it was created long time ago )

[samdark]

@Ragazzo milestone is RC so we agree that it's important enough.

[nsanden]

I need to bring back all Customers with Order count > 0

Is this the same issue?

[cebe]

@nsanden not at all. You want instances of customer, here we are talking about stat relations. Depending on your schema you might get that by just LEFT JOINing the order table...

[nsanden]

Thanks @cebe, always helpful. I was able to finally pull it off doing something like this:

        $query = Customer::find();
        $query->select("tbl_customer.*, COUNT(tbl_order.id) AS order_count");
        $query->leftJoin('tbl_order', 'tbl_order.customer_id = tbl_customer.id');
        $query->groupBy(['tbl_customer.id']);
        $query->having("COUNT(tbl_order.id) > 0");
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

then in model

    public function attributes()
    {
        // add related fields to searchable attributes
        return array_merge(parent::attributes(), ['tbl_customer.order_count']);
    }

seems kind of hacky, am i going about it the wrong way?

[cebe]

@nsanden this is completely offtopic and not a bug report, please create a topic in the forum instead.

[cebe]

Milestone set for 2.0.1, can be in 2.0 if someone is making a pull request.

[qiangxue]

This requires some significant work. Move to 2.1.

[jafaripur]

Now, what is the best solution to get count from relation and a sortable column in gridview!?

[cebe]

maybe add a subquery to the select?
$query->addSelect(['mycount' => $model->getMyRelation()->select('COUNT(*)')])
have not tested it but should work.

EDIT: nope, it does not, sry :)

[omnilight]

My approach for getting statistic:

class DealerReport extends Dealer
{
    public static function find()
    {
        $query = parent::find();

        $query->select([
            'dealers.*',
            'managers.users_count as managers_count',
            'leaders.users_count as leaders_count',
        ]);
        $query->from([
            'dealers' => Dealer::tableName(),
        ]);
        $query->leftJoin([
            'managers' => User::find()->select('dealer_id, COUNT(*) as users_count')->where(['role' => User::ROLE_SALES])->groupBy(['dealer_id'])
        ], 'managers.dealer_id = dealers.id');
        $query->leftJoin([
            'leaders' => User::find()->select('dealer_id, COUNT(*) as users_count')->where(['role' => User::ROLE_DEALER])->groupBy(['dealer_id'])
        ], 'leaders.dealer_id = dealers.id');

        return $query;
    }

    public function attributeLabels()
    {
        return array_merge(parent::attributeLabels(), [
            'managers_count' => 'Number of managers',
            'leaders_count' => 'Number of leaders'
        ]);
    }

    public function attributes()
    {
        return array_merge(parent::attributes(), [
            'managers_count', 'leaders_count'
        ]);
    }
} 

In this example each dealer has plenty on users, some of them are leaders (ROLE_LEADER), other - simple managers (ROLE_SALES).

Subqueries used here gives ability to deal with multiple stat requests at one time

[klimov-paul]

Issue is outdated.
At the present state, you can use yii\db\ActiveQuery::joinWith().
There is nothing else, which can be improved here.

[phtamas]

@klimov-paul I cannot see how does yii\dbActiveQuery::joinWith() solves the problem of eager loading for scalar/aggregate values. It works only with related objects. I wouldn't mind implementing statistical relation handling at application level (if you think it adds too much complexity to the framework code), but it seems that the way ActiveRecord and ActiveQuery currently works makes this task a very difficult one. Please either add a few lines abut this topic to the guide (if you have a working and relatively easy-to-implement solution) or reopen the issue.

[klimov-paul]

There is no way get statistical data, except of composing complex SQL for that.
Following code will do so:

Customer::find()->joinWith('posts')->addSelect('COUNT(posts.id) AS postCount')->groupBy('t.id')->all();

All you need is reserve a public field $postCount inside Customer.

You may override ActiveQuery returned by find() method and introduce a special scope for such code:

class CustomerQuery extends ActiveQuery
{
    public function withPostsCount()
    {
        $this->joinWith('posts')->addSelect('COUNT(posts.id) AS postCount')->groupBy('t.id');
    }
}

Such approach is already described at the docs.

You are asking about statistical query, because back in Yii1 we have a special relation for that, and now we have not.
The Active Record in Yii2 works differently: it no longer joins the tables from relations automatically. It simply can not provide you feature you are asking for.
Also keep in mind there are ActiveRecord solutions for NOSQL data storages, which unable to perform such query at DBMS level at all. What should we do about them?

I inderstand this is quite a sad thing to realize, but it is as it is.

Still, if anyone of @yiisoft/core-developers think this issue requires further threatment like docs updating or anything else - feel free to reopen it.

[klimov-paul]

Added extra docs 13b10bd

[lynicidn]

i think need:

• add new relation type (multiple = true, multiple = false, scalar)
• refactor findFor method (before populate) and add new logic for this new type

problem now, that relation valid is instance of ActiveRelation, but queryScalar return integer

example:
$this->scalarRelation($class, $link, /_moreinfo_/)

and in findFor run $relation->queryScalar(/_more info here_/)

[omnilight]

@lynicidn your suggestion is about retrieving single column of the existed model instead if the hole model instead. This is not what statistical relations are about. Generally we have to calculate some values in runtime using sql and attach this values to the original model. And this is not really the relation.
First approach for retrieving statistical data with your model was shown upper, but if you want to deal with statistics as with relations, you can create view in your database and relate to it

[lynicidn]

@omnilight i don't understand u, sry, but any join relation it relation, aggregate or not it relation!
Ur solve - rewrite ar model via dao case ? and if i want get count related table i should remember sql syntax - 'COUNT({{order}}.id) AS ordersCount' // calculate orders count ? it's real oop?

[SamMousa]

Couldn't my virtual fields proposal solve this?

https://github.com/yiisoft/yii2/issues/8316

[vercotux]

Not sure if something changed since, but I tried following https://github.com/yiisoft/yii2/issues/2179#issuecomment-91845257 and the call Customer::find()->joinWith('posts')->addSelect('COUNT(posts.id) AS postCount')->groupBy('t.id')->all(); did not work at all. The table 't' does not exist, and nor does the attribute 'posts.id'. But more importantly, the documentation clearly says that you need to include '*' otherwise _addSelect()_ won't work:

Note, that if select() has not been specified before, you should include * explicitly if you want to select all remaining columns too:

This worked instead:

Customer::find()->joinWith('posts')->select(['customer.*','COUNT(post.id) AS postsCount'])->groupBy('customer.id')->all();

[SamMousa]

@vercotux I think joinWith does not necessarily do a join query. Instead you should use "innerJoinWith" or one of the others.

[mcfoi]

I too strived to make all this work.. ..but finally succeeded in showing in GridView the count of the number of License(s) owned by a User2016.
Here is my working recipe.
The User2016 model has a relation with License returning the count:

   class User2016 extends ActiveRecord
   {
   ...
    // This will be used in "joinWith" part of $query
    public function getLicenses()
    {
        return $this->hasMany(License::className(),['id_users2016' => 'id']);
    }
    // This will be in $dataProvider->sort step
    public function getCountlicense()
    {
        return $this->hasMany(License::className(),['id_users2016' => 'id'])->count();
    }
   ...
   }

The License model has a relation with the User2016.. ..but it is not involved in solving the specific case.

Inside the UserController, in the actionIndex() function, I define the $dataProvider:

    public function actionIndex()
    {
        $query = User2016::find()
            ->joinWith('licenses')
            ->select(
                [User2016::tableName().'.*','COUNT('.License::tableName().'.id) AS licenseCount']
                )
            ->groupBy(User2016::tableName().'.id');

        /* A dataprovider with all License and related User */
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pagesize' => 8,
            ],
        ]);

        /* Here I bind the User model function "getCountlicense" with the column "licenseCount" created in $query*/
        $dataProvider->sort->attributes['countlicense'] = [
            'asc' => ['licenseCount' => SORT_ASC],
            'desc' => ['licenseCount' => SORT_DESC],
        ];

            $pagination = new Pagination([
                'defaultPageSize' => 20,
                    'totalCount' => $query->count(),
             ]);

            return $this->render('index', [
                    'pagination' => $pagination,
            'dataProvider' => $dataProvider
            ]);
    }

Finally.. ..in the view, I add the GridView:

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        'id',
        ...
        [
            'attribute' => 'countlicense',
            'format' => 'text',
            'label' => '# Licenses',
        ],
        ...
    ],
]) ?>

[shining228]

When I want one more count value, try to join anather table like below, but it not work:
<?php $customers = Customer::find()->select([ '{{customer}}.*', 'COUNT({{order}}.id) AS ordersCount', 'COUNT({{comment}}.id) AS commentsCount' ])->joinWith('orders')->groupBy('{{customer}}.id') ->all(); ?>

[ablaidev]

Если вы хотите чтобы поиск и сортировка по количество свзязанных таблиц была быстрой,
то лучше организовать грамотное сохранение количество в самой базе данных...
То есть в таблице "Категорий новостей" хранить количество "Новостей" по этой "Категорий"...
Но это чревато последствиями не актуальных данных так как новость могут добавить не только с вашего приложения и даже не используя ваш API...

Для уверенности в том что данные актуальны, можно создать какое не будь представление в БД..., к архетектуре которой вас могут не пустить). При подсчете количество нескольких связей по LEFT JOIN, результат выходит не корректным.

Как вариант можно использовать вот такой код в модели для Поиска.

Метод search в модели поиска:

/**
 * Создает экземпляр поставщика данных с поисковым запросом
 * @return ActiveDataProvider
 */
public function search($params = [])
{
    $query = static::find()
        ->with(['user.room.building.city'])
        ->addSelect([
            'problem_create_count' => '(SELECT COUNT(pc.id) FROM ' . Extra::tableName() . ' pc WHERE pc.parent_id = w.id AND pc.type_id=' . Work::TYPE_PROBLEM_CREATE . ')',
            'problem_update_count' => '(SELECT COUNT(pu.id) FROM ' . Extra::tableName() . ' pu WHERE pu.parent_id = w.id AND pu.type_id=' . Work::TYPE_PROBLEM_UPDATE . ')',
            'complaint_count' => '(SELECT COUNT(c.id)  FROM ' . Extra::tableName() . '  c WHERE  c.parent_id = w.id AND  c.type_id=' . Work::TYPE_COMPLAINT . ')'
        ]);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => ['defaultOrder' => ['id' => SORT_DESC]],
    ]);

    $dataProvider->getSort()->attributes['user_name'] = ['asc' => ['u.last_name' => SORT_ASC, 'u.first_name' => SORT_ASC], 'desc' => ['u.last_name' => SORT_DESC, 'u.first_name' => SORT_DESC]];
    $dataProvider->getSort()->attributes['user_city'] = ['asc' => ['c.name' => SORT_ASC], 'desc' => ['c.name' => SORT_DESC]];
    $dataProvider->getSort()->attributes['problem_create_count'] = ['asc' => ['problem_create_count' => SORT_ASC], 'desc' => ['problem_create_count' => SORT_DESC]];
    $dataProvider->getSort()->attributes['problem_update_count'] = ['asc' => ['problem_update_count' => SORT_ASC], 'desc' => ['problem_update_count' => SORT_DESC]];
    $dataProvider->getSort()->attributes['complaint_count']      = ['asc' => ['complaint_count' => SORT_ASC], 'desc' => ['complaint_count' => SORT_DESC]];

    $this->load($params);
    if (!$this->validate()) { $query->where('0=1'); return $dataProvider; }
    $dataProvider->getPagination()->setPageSize($this->pageSize);

    if ($this->isSearchBy('user_name') || $this->isSearchBy('user_city')) $query->joinWith(['user u']);
    if ($this->isSearchBy('user_city')) $query->joinWith(['user.room.building.city c']);

    $query->andFilterWhere([
        'w.id' => $this->id,
        'w.is_working' => $this->is_working,
        'w.created_by' => $this->created_by,
    ]);

    $query->andFilterWhere(['>=', 'w.call_count', $this->call_count]);
    $query->andFilterWhere(['>=', 'w.call_count_help', $this->call_count_help]);
    $query->andFilterWhere(['>=', 'w.call_count_help_repeat', $this->call_count_help_repeat]);
    $query->andFilterWhere(['>=', 'w.mail_count', $this->mail_count]);
    $query->andFilterWhere(['>=', 'w.mail_count_max', $this->mail_count_max]);
    $query->andFilterWhere(['>=', 'w.visit_count', $this->visit_count]);
    $query->andFilterWhere(['>=', 'w.rnu_count', $this->rnu_count]);
    $query->andFilterWhere(['>=', 'w.auth_count', $this->auth_count]);
    $query->andFilterWhere(['>=', 'w.gu_count', $this->gu_count]);
    $query->andFilterWhere(['>=', 'w.ball', $this->ball]);

    $query->andFilterWhere(['like', 'CONCAT(last_name, " ", first_name, " ", u.username)', $this->user_name]);
    $query->andFilterWhere(['c.id' => $this->user_city]);
    $query->andFilterWhere(['between', 'w.day_at', $this->day_from, $this->day_to]);
    if ($this->problem_create_count) { $query->andHaving(['>=', 'problem_create_count', $this->problem_create_count]); }
    if ($this->problem_update_count) { $query->andHaving(['>=', 'problem_update_count', $this->problem_update_count]); }
    if ($this->complaint_count)      { $query->andHaving(['>=', 'complaint_count', $this->complaint_count]); }

    return $dataProvider;
}

Результат:

SELECT 
    `w`.*, 
    (SELECT COUNT(pc.id) FROM d_dcc_premium_work_extra pc WHERE pc.parent_id = w.id AND pc.type_id=7) AS `problem_create_count`, 
    (SELECT COUNT(pu.id) FROM d_dcc_premium_work_extra pu WHERE pu.parent_id = w.id AND pu.type_id=8) AS `problem_update_count`, 
    (SELECT COUNT(c.id)  FROM d_dcc_premium_work_extra  c WHERE  c.parent_id = w.id AND  c.type_id=9) AS `complaint_count` 
FROM `d_dcc_premium_work` `w` 
HAVING `problem_create_count` >= '5' 
ORDER BY `problem_create_count` DESC 
LIMIT 20

[SamMousa]

I have this working for both eager and lazy loading. It supports any type of relationship using subquery in the select.
https://github.com/MarketFlow/yii2-virtual-fields

This allows for a clean implementation.

[dungphanxuan]

I have table car carry, and model car model has one car carry. Model car has one car model.
So how can i count car number of car carry?

[yii-bot]

Thank you for your question.
In order for this issue tracker to be effective, it should only contain bug reports and feature requests.

We advise you to use our community driven resources:

• The support forum
• The IRC chat room
• The Gitter chat room
• The Definitive Guide to Yii

If you are confident that there is a bug in the framework, feel free to provide information on how to reproduce it. This issue will be closed for now.

_This is an automated comment, triggered by adding the label question._