How to get count from many relations in a Grid Yii2 Search Model

Question

I have many relations in a model. I want to show total number of orders, products, order received in a grid view using search model for filter and export grid but I am not able to figure out how to get counts with sort. I have following relations.

public function getProducts()
{
    return $this->hasMany(Product::className(), ['user_id' => 'user_id']);
}
public function getShopImages(){
    return $this->hasMany(ShopImage::className(), ['user_id' => 'user_id']);
}
/**
 * @return \yii\db\ActiveQuery
 */
public function getOrders()
{
    // Customer has_many Order via Order.customer_id -> id
    return $this->hasMany(Order::className(), ['user_id' => 'user_id']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getOrderrs()
{
    // Customer has_many Order via Order.customer_id -> id
    return $this->hasMany(Order::className(), ['merchant_id' => 'user_id']);
}

I need count of each one. Any Idea, how to do it ?

Answer 1

You can just call the methods like this:

$model->getProducts()->count();

How this works:

When you call the actual method returns a yii\db\ActiveQueryInterface instance, so you can use methods like count(), all(), one(), etc.

Usually when you access the releationship you would do it as if it was a property from you model class like this $model->products. This way you get the result of the yii\db\ActiveQueryInterface the method actually returns, evaluated with all() or one() depending if you used hasMany or hasOne.

Answer 2

Thanks，And this link from github helps me a lot too.

    +
     +## Selecting extra fields
     +
     +When Active Record instance is populated from query results, its attributes are filled up by corresponding column
     +values from received data set.
     +
     +You are able to fetch additional columns or values from query and store it inside the Active Record.
     +For example, assume we have a table named 'room', which contains information about rooms available in the hotel.
     +Each room stores information about its geometrical size using fields 'length', 'width', 'height'.
     +Imagine we need to retrieve list of all available rooms with their volume in descendant order.
     +So you can not calculate volume using PHP, because we need to sort the records by its value, but you also want 'volume'
     +to be displayed in the list.
     +To achieve the goal, you need to declare an extra field in your 'Room' Active Record class, which will store 'volume' value:
     +
     +```php
     +class Room extends \yii\db\ActiveRecord
     +{
     +    public $volume;
     +
     +    // ...
     +}
     +```
     +
     +Then you need to compose a query, which calculates volume of the room and performs the sort:
     +
     +```php
     +$rooms = Room::find()
     +    ->select([
     +        '{{room}}.*', // select all columns
     +        '([[length]] * [[width]].* [[height]]) AS volume', // calculate a volume
     +    ])
     +    ->orderBy('volume DESC') // apply sort
     +    ->all();
     +
     +foreach ($rooms as $room) {
     +    echo $room->volume; // contains value calculated by SQL
     +}
     +```
     +
     +Ability to select extra fields can be exceptionally useful for aggregation queries.
     +Assume you need to display a list of customers with the count of orders they have made.
     +First of all, you need to declare a `Customer` class with 'orders' relation and extra field for count storage:
     +
     +```php
     +class Customer extends \yii\db\ActiveRecord
     +{
     +    public $ordersCount;
     +
     +    // ...
     +
     +    public function getOrders()
     +    {
     +        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
              //->from(['your_table_alias'=>Order::className()])
     +    }
     +}
     +```
     +
     +Then you can compose a query, which joins the orders and calculates their count:
     +
     +```php
     +$customers = Customer::find()
     +    ->select([
     +        '{{customer}}.*', // select all customer fields
     +        'COUNT({{order}}.id) AS ordersCount' // calculate orders count
     +    ])
     +    ->joinWith('orders') // ensure table junction
     +    ->groupBy('{{customer}}.id') // group the result to ensure aggregation function works
     +    ->all();

And in your Search Model ,you should do this

class Search extends yourModel
{
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            your rules.......
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = Fromto::find()->joinWith('orders')->groupBy('{{customer}}.Id');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        $query->andFilterWhere([
            'Id' => $this->Id,
            '{{customer}}.customer_column' => $this->your_column,
        ]);

        return $dataProvider;
    }
}

And in View

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],

        'Id',
        [
            'attribute'=>'your_column',
            'label'=>'your label',
            'value'=>function($model){
                return $model->orders->order_column;
            }
        ],
        .....

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

Answer 3

Please look into below links. It's help you.

a. https://github.com/yiisoft/yii2/issues/2179

b. http://www.yiiframework.com/forum/index.php/topic/62772-how-to-get-count-in-relation-table-in-yii2-activerecord/

public function getProducts(){
 return $this->hasMany(Product::className(), ['user_id' => 'user_id'])->count();}