Cannot sort gridview on related fields

Yii 2.0 General Discussions
1

Hello, I’m in trouble with an old question, discussed many times.
I have the table abilitazione with field indicatore_id related to indicatore table. Table indicatore is then related with categoria thru field categoria_id.
This is the search class:

<?php
namespace app\models;
use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;

class AbilitazioneSearch extends Abilitazione {
    
    public function rules() {
        return [
            [['id'], 'integer'],
            [['indicatore_id', 'livello_id', 'gruppo_id', 'tiporuolo_id'], 'integer'],
            [['commento', 'visibilita'], 'integer'],
            [['indicatore.categoria_id'], 'safe'],
        ];
    }

    public function attributes() {
        return array_merge(
                parent::attributes(),
                [
                    'indicatore.categoria_id',
                ]
        );
    }

    public function search($params) {
        $query = self::find();
        $query->joinWith('gruppo');
        $query->joinWith('indicatore');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);
        
        $dataProvider->sort->attributes['indicatore.categoria_id'] = [
            'asc' => ['indicatore.categoria_id' => SORT_ASC],
            'desc' => ['indicatore.categoria_id' => SORT_DESC],
        ];

        $this->load($params);

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

        $query->andFilterWhere([
            'abilitazione.id' => $this->id,
            'abilitazione.gruppo_id' => $this->gruppo_id,
            'abilitazione.indicatore_id' => $this->indicatore_id,
            'abilitazione.commento' => $this->commento,
            'abilitazione.visibilita' => $this->visibilita,
            'indicatore.categoria_id' => $this->getAttribute('indicatore.categoria_id'),
        ]);

        return $dataProvider;
    }

… and this is the column inserted in the view:

    [
        'attribute' => 'indicatore.categoria_id',
        'value' => 'indicatore.categoria.descrizione',
        'header' => 'Categoria',
        'filter' => app\models\Categoria::listaRecord('id', 'descrizione', null, [['ordinato', 'ordine']]),
    ],

The issie is that the column categoria is not sortable (the filtering runs correctly instead).
Can someone helps me? Thanks

2

I’m not sure but you should try this

public categoria_id;

        $dataProvider->sort->attributes['categoria_id'] = [
            'asc' => ['indicatore.categoria_id' => SORT_ASC],
            'desc' => ['indicatore.categoria_id' => SORT_DESC],
        ];
3

I’m also not sure but you can try to join ‘categoria’ explicitly.

public function search($params) {
        // [...]
        $query->joinWith('indicatore')->joinWith('categoria');
4

Nothing changed

5

@ Bartek
Cannot use your code because abilitazione hasn’t a direct relation with categoria; this produce an error.
Trying this

$query->joinWith('indicatore.categoria');

no error is shown, but the issue remain the same.

6

I presume indicatore.categoria_id is the actual table and column names in the db.

7

Try to add it in form of experiment. You can do it by chaining relations:
https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#multi-table-relations

8

C o r r e c t

9

Indeed table categoria is not so relevant. The field categoria_idis from table indicatore, so I think the join with categoria is not needed at all.