Sorting related data in GridView

Hi!

I´m sorry for asking a question that has been answered many times before on different forums. I just can´t seem to get this to work and think it is just some detail I´m failing on.

Simple problem, I have a table called "members" (note the plural s on the end) and a table called "nominations". The member table has a nomination_id column meaning that each member record can have one related nomination. I want to display a sortable related nominations.name in a gridview, othervwise listing members. Should be easy enough right?

I read several guides on the topic but it still fails.

Model: Members


    public function getNomination()

    {

        return $this->hasOne(Nominations::className(), ['id' => 'nomination_id']);

    }

Model: MembersSearch


use yii\data\ActiveDataProvider;


// add the public attributes that will be used to store the data to be search

    public $nomination;

 

    // set the rules to make those attributes safe

    public function rules()

    {

        return [

            [['nomination'], 'safe'],

        ];

    }




// Setup special search method to allow filter and sorting of data

    public function search($params)

    {

        // create ActiveQuery

        $query = Members::find();

        // Important: lets join the query with our previously mentioned relations

        // I do not make any other configuration like aliases or whatever, feel free

        // to investigate that your self

        $query->joinWith(['nomination']);

     

        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);

     

        // Important: here is how we set up the sorting

        // The key is the attribute name on our "TourSearch" instance

        $dataProvider->sort->attributes['nomination'] = [

            // The tables are the ones our relation are configured to

            // in my case they are prefixed with "tbl_"

            'asc' => ['nominations.name' => SORT_ASC],

            'desc' => ['nominations.name' => SORT_DESC],

        ];


        // No search? Then return data Provider

        if (!($this->load($params) && $this->validate())) {

            return $dataProvider;

        }

        // We have to do some search... Lets do some magic

        $query->andFilterWhere([

            //... other searched attributes here

        ])

        // Here we search the attributes of our relations using our previously configured

        // ones in "TourSearch"

        ->andFilterWhere(['like', 'nomination.name', $this->nomination]);

     

        return $dataProvider;

    }



Controller: MembersController


    public function actionList()

    {

        $searchModel = new MembersSearch();

        $query = MembersSearch::find()->where(['lodge_id' => Yii::$app->user->identity->lodge_id]);

        $dataProvider = $searchModel->search($query);


        // Render the view

        return $this->render('list', 

            array(

                "dataProvider" => $dataProvider,

                "searchModel" => $searchModel,

            ));

    }

View: /members/list.php


echo GridView::widget([

    'dataProvider' => $dataProvider,

    'columns' => [

    	['attribute' => 'firstname', 'label' => 'Förnamn'],

    	['attribute' => 'lastname', 'label' => 'Efternamn'],

    	['attribute' => 'nominations', 'value' => 'nominations.name'],

    	['attribute' => 'phone_1', 'label' => 'Mobil'],

    	['attribute' => 'email', 'label' => 'E-post'],

    	[

        'class' => ActionColumn::className(),

        'template' => '{detail} {update}',

        // you may configure additional properties here

        ],

    ],

    'layout' => '{items}{pager}',

]);

The different guides I read have a little bit of different approches. Non of them explains how to set up the DataProvider in the controller. I also feel unsure of when to use the plural s that I foolishly added to the end of each table. If someone has the time to help me get this to work I would appreciate it very much.

With the current code I get the error "Cannot use object of type yii\db\ActiveQuery as array" in /var/www/vendor/yiisoft/yii2/base/Model.php (747)

Thanks in advance!

There’s a collision between a relation called “nomination” and a normal member variable “nomination”.

Just use another word for the variable, e.g. "$nominationName" or something like that.

[EDIT]

And …




    'columns' => [

        ...

        // ['attribute' => 'nominations', 'value' => 'nominations.name'],

        ['attribute' => 'nomination.name', 'label' => 'Nomination Name'],

        ...



Thanks Softark, you are correct. I also did a couple of other errors. I finally did solve it and for other that might feel uncertain on how to do this I would recommend this guide.

http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0

The only thing missing in that guide is the code in the controller.


public function actionList()

    {

        $searchModel = new MembersSearch();

        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);


        // Render the view

        return $this->render('list', 

            array(

                "dataProvider" => $dataProvider,

                "searchModel" => $searchModel,

            ));

    }