Yii2 filter Sum data in GridView

Question

I'm trying display and filter data which I got via SQL SUM operator. I have 2 table employee and department. Table employee contains department_id filed and salary filed. I need display all department and total SUM salary for every department. I followed this guide, but GridView does not display any data. Here is action:

public function actionIndex()
{
    $searchModel = new DepartmentFrontendSearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
    return $this->render('index',
        [
            'dataProvider' => $dataProvider,
            'searchModel' => $searchModel,

        ]
    );
}

Model:

class DepartmentFrontendSearch extends DepartmentFrontend

public $employee_count;
public $salary;


public function rules() {
    return [

        [['employee_count','salary','name'], 'safe']
    ];
}

public function search($params) {

    $query = DepartmentFrontend::find();
    $subQuery = Employee::find()
        ->select('department_id, SUM(salary) as salary_amount')
        ->groupBy('department_id');
    $query->leftJoin(['salarySum' => $subQuery], 'salarySum.department_id = id');

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
    $dataProvider->setSort([
        'attributes' => [
            'name',
            'salary'
        ]
    ]);

    $this->load($params);

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

    $query->andFilterWhere(['like', 'name', $this->name]);
    $query->andWhere(['salarySum.salary_amount' => $this->salary]);
    return $dataProvider;
}

GRID:

   echo GridView::widget([
   'dataProvider' => $dataProvider,
   'filterModel' => $searchModel,
   'columns' => [
       ['class' => 'yii\grid\SerialColumn'],
       'salary_amount',
       'employee_count',
       'name',

   ],

]);

So, can anybody tell me what I did wrong?

scaisEdge scaisEdge · Accepted Answer · 2016-11-04T08:06:33

If You need a filter for the alias salary_amount you should add a specific var for this (do the fact salary_amount is an alias for sum(salary) the var $salary is not useful)

public $employee_count;
public $salary;
public $salary_amount;

otherwise use salary as alias too

the in your filter you are using

$query->andWhere(['salarySum.salary_amount' => $this->salary]);

But salary_amount is the result of an aggregation so you should use having(SUM(salary) = $this->salary_amount)

 $query->having('SUM(salary) = ' . $this->salary_amount);

Yii2 filter Sum data in GridView

1 Answers