yii2 gridview filter a count/sum on a second table column

Question

I have these related tables: order, order_product (order_id, product_id, quantity, product_price) and product. I am using yii2 grid view to display the order model with it's total amount that can be calculated in sql like select SUM(p.quantity*p.product_price) as total from order_product p GROUP by order_id or with a php getter and the number of products ordered that I can get easily with a hasmany. My problem is the gridview filter. How can I set the search and sort of these columns in the gridview?

@Mohan thanks for ur answer but i've already tried that and it gave me an Integrity constraint violation that i couldn't handle here it is "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in on clause is ambiguous The SQL being executed was: SELECT COUNT() FROM order LEFT JOIN customer ON order.customer_id = customer.id LEFT JOIN (SELECT order_id, SUM(quantityproduct_price) as total FROM order_product GROUP BY order_id) orderSum ON orderSum.order_id = id" — leila
Can provide you'r code of modelSearch.php . because i have already done with that link. — Mohan

leila leila · Accepted Answer · 2016-09-28T11:14:31

I found the answer.

<?php

namespace common\models;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use common\models\Order;
use common\models\OrderProduct;
class OrderSearch extends Order
{
    public $total;
    public $nbProd;
    public $client;
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['total', 'nbProd', 'client'], 'safe'],
            [['nbProd'], 'number'],
            [['client'], 'string'],
        ];
    }
    public function search($params)
    {
        $query = Order::find();
        $query->joinWith(['customer']);
        $subQuery = OrderProduct::find()
        ->select('order_id, SUM(quantity*product_price) as total,               count(product_id) as nbProd')
        ->groupBy('order_id');
        $query->leftJoin(['orderSum' => $subQuery], 'orderSum.order_id = order.id');
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort'=> ['defaultOrder' => ['id'=>SORT_DESC]],
        ]);
        $dataProvider->sort->attributes['client'] = [
            'asc' => ['customer.company' => SORT_ASC],
            'desc' => ['customer.company' => SORT_DESC],
        ];
        $dataProvider->sort->attributes['nbProd'] = [
            'asc' => ['orderSum.nbProd' => SORT_ASC],
            'desc' => ['orderSum.nbProd' => SORT_DESC],
        ];
        $dataProvider->sort->attributes['total'] = [
            'asc' => ['orderSum.total' => SORT_ASC],
            'desc' => ['orderSum.total' => SORT_DESC],
        ];

        $this->load($params);

        if (!$this->validate()) {
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'orderSum.total' => $this->total,
            'orderSum.nbProd' => $this->nbProd,
        ]);

        $query->andFilterWhere(['like', 'customer.name', $this->client]);

        return $dataProvider;
    }
}

yii2 gridview filter a count/sum on a second table column

1 Answers