CODEWITHSUNDEEP
phpyii2
Rui Sousa
Rui Sousa

Reputation: 57

Yii2: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'status' in 'order clause'. Can't filter or sort

I added a custom column(status) to my search model to display OK or NOK based on the values of two other columns. However i'm not able to sort or filter by that column since is not in the database.

I found this thread using afterFind function: Adding an attribute to yii2 active record model that is not in database

I added the column to fase rule of my model

[['status'], 'safe'],

and added to filter

if($this->status) {
        $query->orFilterWhere(['status'=>$this->status]);
    }

but still i can't make it to work.

How can i sort and filter a column that is not in the database, a custom column added in the search model?

[
            'attribute' => 'status',
            'value' => function($model){
                         if($model->Dinheiro_recarregado != $model->valorMoeda){
                            return 'NOK';
                         } else {
                            return 'OK';
                         }
                       },
            'filter' => function ($model) {
                if ($model->status == 'OK' ) {
                    return 'OK';
                } else {
                    return 'NOK';
                }
            },
        ],

that's how the OK and NOK are added to column.

Upvotes: 0

Views: 1293

Answers (2)

Abhishek kandari
Abhishek kandari

Reputation: 196

SELECT CASE 
        WHEN Dinheiro_recarregado = valorMoeda
           THEN 'OK' 
           ELSE 'NOK'
   END as status,* FROM Product order By status ASC

change your query a little like above & then try sorting

Upvotes: 0

Bizley
Bizley

Reputation: 18021

If I correctly assumed that Dinheiro_recarregado and valorMoeda are names of the database table columns try this query condition:

if ($this->status === 'OK') {
    $query->andWhere('Dinheiro_recarregado = valorMoeda');
} elseif ($this->status === 'NOK') {
    $query->andWhere('Dinheiro_recarregado <> valorMoeda');
}

Upvotes: 1

Related Questions