Calculared field in gridview and filter

[Roberto_Blanco]

I need to filter in a gridview by a calculated field. I am not sure if I have to create a public attribute in the model or in the search or how to use it later in the gridview in the view. Do you know any interesting link where this is explained?

[evstevemd (Stefano Mtangoo)]

Can you please explain what you mean? where do you do the calculation, how do you compare the results.

[Roberto_Blanco]

<?= GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [ [ 'label'=>'Recibo Nº', 'attribute'=> 'id', 'headerOptions' => ['style' => 'width:50px;'], 'enableSorting' => true, ], ['label'=>'Apellidos', 'attribute'=>'alumno0.apellidos', 'filter' => 'enableSorting' => true, ], I need to be able to filter by alumn0.apellidos in this gridview, but the problem is that this value comes from a method, that is, there is no field in the model.

[bpanatta (Bruno Panatta)]

What you need is to filter the data that is inside the $dataProvider. This can be achieve by filtering the query that go into your $dataProvider.

The easy way to do it is like the following example:

$query = Alumno::find();
$query->andFilterWhere( ['like', 'apellidos', $apellidos] );

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

@Roberto_Blanco

I am not sure if I have to create a public attribute in the model or in the search or how to use it later in the gridview in the view.

I like to have a Search Model, that when there is attributes unique to the search I just create them in the Search Model as:

• Getter methods when they need to be calculated; or
• Public attributes if they are not calculated.

In Yii2 you can create this kind of getter:

public function getApellidos() {
    return $this->x * $this->y; // Custom calculation
}

And this getter can be “referred” to just as if it was an attribute in your GridView:

'attribute' => 'alumno0.apellidos',

In official docs this is described like this: Note that you may also need to override the magic methods such as __get() , __set() so that the attributes can be accessed like normal object properties."

I also recommend reading Filtering Data Providers using Data Filters and from the official documentation.

[softark]

There are 2 great wikis regarding this topic written by Kartik V.

Filter & Sort by calculated/related fields in GridView Yii 2.0
Filter & Sort by Summary Data in GridView Yii 2.0

Please take your time to read them. I’m sure they are helpful.

[Roberto_Blanco]

Forgive me but I don’t understand.
I have a get method that calculates all the fields of the alumnos table from where I want to show the surnames and the first name and be able to filter in the gridview by them.
This is my get where alumno0.name returns my name and alumno0.lastname returns my last names.

My question is if alumno0 can be used in the search model to produce the corresponding filter.

public function get alumno0 ()
{
return $ this-> hasOne (Alumnos:: className (), [‘id’ => ‘alumno’])
-> viaTable (‘matriculas’, [‘id’ => ‘matricula’]);

}

What I show in the gridview are the receipts from an academy.
One of the fields I want to display and filter is the student’s last name and first name.

[bpanatta (Bruno Panatta)]

This getAlumno0 is a method of another model?
What is the query that goes into your “dataProvider”?

[Roberto_Blanco]

the method is from the model if and the query is this.

getAlumo0 is a method of the recibos model and this method returns the name and surname of Alumnos.

alumno0.apellidos
alumno0.nombre

public function actionIndex()
    {
        $searchModel = new RecibosSearch();
        $datos = new Recibos();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'datos' => $datos,
        ]);
    }

this is my RecibosSearch

class RecibosSearch extends Recibos
{
     public $filtro_alumno;
    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['id', 'matricula', 'mes', 'anyo', 'estado', 'reducido'], 'integer'],
            [['emision','filtro_alumno'], 'safe'],
            [['importe'], 'number'],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function scenarios()
    {
              return Model::scenarios();
    }

    public function search($params)
    {
        $query = Recibos::find();
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);
         $dataProvider->setSort([
        'attributes' => [
            'id',
            'filtro_alumno', 
            'maticula',
            'emision',
            'mes',
            'anyo',
            'estado',
            'reducido',
            'importe',
            ]
        ]);

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

        $query->andFilterWhere([
            'id' => $this->id,
            //'filtro_alumno' => $this->filtro_alumno,
            'matricula' => $this->matricula,
            'emision' => $this->emision,
            'mes' => $this->mes,
            'anyo' => $this->anyo,
            'estado' => $this->estado,
            'reducido' => $this->reducido,
            'importe' => $this->importe,
        ]);

        $query->andWhere('alumno0.apellidos "%' . $this->filtro_alumno.'%"');
  
        return $dataProvider;
    }
}

[softark]

Here’s another great wiki regarding the GridView handling.
Displaying, Sorting and Filtering Model Relations on a GridView

It’s more basic than the two that I mentioned in the previous post, and it’s a must-read.

@Roberto_Blanco you refer to almuno0.apellidos as a calculated field, but it’s misleading. It’s more appropriate to call it a field in a related model. So you can apply this wiki article to your case.

The point is joining the related table using joinWith.

        // $query = Recibos::find();
        $query = Recibos::find()->joinWith(['alumno0']);
...
       // $query->andWhere('alumno0.apellidos "%' . $this->filtro_alumno.'%"');
       $query->andWhere(['like', 'almuno.apellidos', $this->filtro_alumno]); 

I’m not very sure if 'almuno.apellidos' is OK or not.

[Roberto_Blanco]

@softark

$query->andWhere([‘like’, ‘almuno.apellidos’, $this->filtro_alumno]);

Thank you, thank you, thank you, thank you. I had been trying to do this for a long time and I finally have it.
Many thanks.