Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How create statistical query in relation? #2179

Closed
incrize opened this issue Jan 27, 2014 · 35 comments
Closed

How create statistical query in relation? #2179

incrize opened this issue Jan 27, 2014 · 35 comments
Milestone

Comments

@incrize
Copy link

@incrize incrize commented Jan 27, 2014

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
Copy link
Member

@samdark samdark commented Jan 27, 2014

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();
@samdark samdark closed this Jan 27, 2014
@incrize
Copy link
Author

@incrize incrize commented Jan 27, 2014

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

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

$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
Copy link
Contributor

@Ragazzo Ragazzo commented Mar 9, 2014

bump, @qiangxue what your thoughts about this one?

@cebe
Copy link
Member

@cebe cebe commented Mar 9, 2014

So this is basically about eager loading for statistical queries?

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Mar 9, 2014

yes

@cebe cebe added this to the 2.0 RC milestone Mar 9, 2014
@cebe
Copy link
Member

@cebe cebe commented Mar 9, 2014

set for RC, will think about it.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented May 22, 2014

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
Copy link
Member

@cebe cebe commented May 22, 2014

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

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented May 22, 2014

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
Copy link
Member

@samdark samdark commented May 22, 2014

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

@nsanden
Copy link
Contributor

@nsanden nsanden commented May 30, 2014

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

Is this the same issue?

@cebe
Copy link
Member

@cebe cebe commented May 30, 2014

@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
Copy link
Contributor

@nsanden nsanden commented May 30, 2014

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
Copy link
Member

@cebe cebe commented May 31, 2014

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

@cebe
Copy link
Member

@cebe cebe commented Jul 24, 2014

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

@cebe cebe modified the milestones: 2.0.1, 2.0 RC Jul 24, 2014
@qiangxue
Copy link
Member

@qiangxue qiangxue commented Jul 24, 2014

This requires some significant work. Move to 2.1.

@jafaripur
Copy link
Contributor

@jafaripur jafaripur commented Nov 13, 2014

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

@cebe
Copy link
Member

@cebe cebe commented Nov 13, 2014

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
Copy link
Contributor

@omnilight omnilight commented Dec 11, 2014

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
Copy link
Member

@klimov-paul klimov-paul commented Apr 9, 2015

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

@klimov-paul klimov-paul closed this Apr 9, 2015
@phtamas
Copy link

@phtamas phtamas commented Apr 11, 2015

@klimov-paul I cannot see how does yii\db\ActiveQuery::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
Copy link
Member

@klimov-paul klimov-paul commented Apr 11, 2015

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.

@cebe cebe removed this from the 2.1.x milestone Apr 11, 2015
@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Apr 23, 2015

Added extra docs 13b10bd

@klimov-paul klimov-paul added this to the 2.0.4 milestone Apr 23, 2015
@lynicidn
Copy link
Contributor

@lynicidn lynicidn commented Apr 23, 2015

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
Copy link
Contributor

@omnilight omnilight commented Apr 23, 2015

@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
Copy link
Contributor

@lynicidn lynicidn commented Apr 24, 2015

@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
Copy link
Contributor

@SamMousa SamMousa commented May 7, 2015

Couldn't my virtual fields proposal solve this?

#8316

@vercotux
Copy link

@vercotux vercotux commented Jan 27, 2016

Not sure if something changed since, but I tried following #2179 (comment) 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
Copy link
Contributor

@SamMousa SamMousa commented Feb 1, 2016

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

@mcfoi
Copy link

@mcfoi mcfoi commented Mar 2, 2016

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
Copy link

@shining228 shining228 commented Jul 1, 2016

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
Copy link

@ablaidev ablaidev commented Oct 29, 2016

Если вы хотите чтобы поиск и сортировка по количество свзязанных таблиц была быстрой,
то лучше организовать грамотное сохранение количество в самой базе данных...
То есть в таблице "Категорий новостей" хранить количество "Новостей" по этой "Категорий"...
Но это чревато последствиями не актуальных данных так как новость могут добавить не только с вашего приложения и даже не используя ваш 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
Copy link
Contributor

@SamMousa SamMousa commented Oct 29, 2016

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
Copy link
Contributor

@dungphanxuan dungphanxuan commented Mar 6, 2017

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?

@SilverFire SilverFire added the question label Mar 6, 2017
@yii-bot
Copy link

@yii-bot yii-bot commented Mar 6, 2017

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:

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
You can’t perform that action at this time.