Filtering column relation between relation table [solved]

Yii 2.0 General Discussions
1

I have two tables called SalaryPayment and Employee (the relation between this table are salarypayment.employee_id to employee.id) simple
I did the same search to my previous codes and it worked, but this somehow cannot filter the adding column from employee which is emp_code in table employee.
what do i miss ? thanks
here what i do :
in SalaryPaymentSearch Model

class SalaryPaymentSearch extends SalaryPayment
{

    // additional properties for searching fields
    public function attributes() {
        return array_merge(parent::attributes(), ['employee.emp_code']);
    }
    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['id', 'transfer', 'created_by', 'updated_by'], 'integer'],
            [['trx_id', 'employee_id', 'start_date', 'end_date', 'remark', 
              'created_time', 'updated_time', 'employee.emp_code'], 'safe'],
        ];
    }

    /**
     * {@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 = SalaryPayment::find();
        $query->joinWith(['employee']);

        // add conditions that should always apply here

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

        $dataProvider->sort->attributes['employee.emp_code'] = [
            'asc' => ['employee.emp_code' => SORT_ASC],
            'desc' => ['employee.emp_code' => SORT_DESC]
        ];

        $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;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            ...
        ]);

        $query->andFilterWhere(['like', 'trx_id', $this->trx_id])
            ->andFilterWhere(['like', 'remark', $this->remark])
            ->andFilterWhere(['like', 'employee.name', $this->employee_id])
            ->andFilterWhere(['like', 'employee.emp_code', $this->getAttribute('employee', 'emp_code')]);

        return $dataProvider;
    }
}

and this is my index file

$gridColumns = [        
        [
            'class' => 'kartik\grid\ActionColumn',
            'dropdown' => false,
            'vAlign'=>'middle',            
        ],
        //'id',
        'trx_id',
        'employee.emp_code',
        [
            'attribute' => 'employee_id',
            'value' => 'employee.name'
        ],
        'start_date',
        'end_date',
        [
            'attribute' => 'salary_per_day',
            'format' => ['decimal', 0],
            'hAlign' => 'right'
        ],
      ...
];

<?= GridView::widget([
        'id' => 'kv-grid-salary-payment',
        'dataProvider'=>$dataProvider,
        'filterModel'=>$searchModel,
        'columns'=>$gridColumns,
        'containerOptions'=>['style'=>'overflow: auto'], 
        'headerRowOptions'=>['class'=>'kartik-sheet-style'],
        'filterRowOptions'=>['class'=>'kartik-sheet-style'],
        'pjax'=>true, // pjax is set to always true for this demo     
        'toolbar' => [
            [
                'content' =>
                    Html::button('<i class="fa fa-plus"></i>', [
                        'class' => 'btn btn-success',
                        'title' =>  Yii::t('kvgrid', 'Create Salary Payment'),
                        'onclick'=> "window.location.href = '" . \Yii::$app->urlManager->createUrl(['/salary-payment/create']) . "';"
                    ]) . ' ' .
                    Html::a('<i class="fa fa-refresh"></i>', ['/salary-payment'], [
                        'class' => 'btn btn-default',
                        'title'=>Yii::t('kvgrid', 'Reset Grid'),
                        'data-pjax' => 0,
                    ]),
                'options' => ['class' => 'btn-group mr-2']
            ],
            '{export}',
            '{toggleData}'
        ],
        'export'=>[
            'fontAwesome'=>true
        ],        
        // parameters from the demo form
        'bordered'=>true,
        'striped'=>false,
        'condensed'=>true,
        'responsive'=>false,
        'hover'=>true,
        'showPageSummary'=>false,
        'panel'=>[
            'type'=>GridView::TYPE_PRIMARY,            
        ],
        'persistResize'=>false,
        'exportConfig'=>$defaultExportConfig,
    ]); ?>

and these are the logs i viewed, didn’t search with attribute emp_code

image
image905×416 25 KB

image
image1550×256 18.8 KB

image
image1613×295 25.6 KB

2

You need to show how you have defined your relation.
Meanwhile try to put andWhere instead of filterwhere and see what happens. Filterwhere will ignore if it is empty variables, andWhere will not. That should help warming up the debugging process.

Personally I love to be explicit, so I define a field in search model like emp_code and use it. I have never use relations because I want to avoid extra query to just load a relation.

3

here are the relations

    employee model
    public function getSalaryPayments()
    {
        return $this->hasMany(SalaryPayment::class, ['employee_id' => 'id']);
    }

    salarypayment model
    public function getEmployee()
    {
        return $this->hasOne(Employee::class, ['id' => 'employee_id']);
    }

just curios why it doesn’t work because another code is working.

4

I solved the problem with these codes .

explicitly defined the attributes that we want to search in datagrid.

class SalaryPaymentSearch extends SalaryPayment
{
    public $emp_code;
    public function rules()
    {
        return [
            [['id', 'transfer', 'created_by', 'updated_by'], 'integer'],
            [['trx_id', 'employee_id', ... ,'emp_code'], 'safe'],
        ];
    }

public function search($params)
    {
        $query = SalaryPayment::find();
        $query->joinWith(['employee']);

        // add conditions that should always apply here

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

        $dataProvider->sort->attributes['emp_code'] = [
            'asc' => ['emp_code' => SORT_ASC],
            'desc' => ['emp_code' => SORT_DESC]
        ];

        $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;
        }

        // grid filtering conditions
        $query->andFilterWhere([
           // other exact search
        ]);

        $query->andFilterWhere(['like', 'trx_id', $this->trx_id])
            ->andFilterWhere(['like', 'remark', $this->remark])
            ->andFilterWhere(['like', 'employee.name', $this->employee_id])
            ->andFilterWhere(['like', 'employee.emp_code', $this->emp_code]);

        return $dataProvider;
    }
5

This should work. But when employee have no relationship will crash. If you use PHP8 then use
andFilterWhere(['like', 'employee.emp_code', $this->employee->emp_code ?? null]);

for the lower use shorthand if for explicit checks

$query->andFilterWhere(['like', 'trx_id', $this->trx_id])
           //.......
            ->andFilterWhere(['like', 'employee.emp_code', $this->employee->emp_code]);
6

got error for this code.

image
image1046×696 31.5 KB

7

Check updated answer