[SOLVED] How to display column of joinWith in GridView?

Yii 2.0 General Discussions
1

I tried everything I can google… but still cannot get my join column shown on gridview

What is the syntax to show a column of a joinWith query in GridView?

Model




    public function getOwner()

    {

        return $this->hasOne(User::className(), ['id' => 'owner_id'])->viaTable('property_owner_map', ['property_id' => 'id']);

    }


    public function getRealties()

    {

        return $this->hasMany(Company::className(), ['id' => 'realty_id'])->viaTable('property_realty_assignment', ['property_id' => 'id']);

    }

Controller




        $dataProvider = new ActiveDataProvider([

            'query' => Property::find()

                    ->joinWith(['owner','realties'])

                    ->where(['user.id'=>$id])

                    ->select('property.*, company.name as realtyname')

            ]);


        return $this->render('appindex', [

            'dataProvider' => $dataProvider,

        ]);

View




 <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'columns' => [

            ['class' => 'yii\grid\SerialColumn'],

            'id',

            'name',

            'type',


              'realties.realtyname',  <---- DONT WORK

 ['attribute' => 'Realty Name', 'value' => 'realties.realtyname'],  <------ DON'T WORK


            ['class' => 'yii\grid\ActionColumn'],

        ],

    ]); ?>

SQL




SELECT `property`.*, `company`.`name` AS `realtyname` FROM `property` LEFT JOIN `property_owner_map` ON `property`.`id` = `property_owner_map`.`property_id` LEFT JOIN `user` ON `property_owner_map`.`owner_id` = `user`.`id` LEFT JOIN `property_realty_assignment` ON `property`.`id` = `property_realty_assignment`.`property_id` LEFT JOIN `company` ON `property_realty_assignment`.`realty_id` = `company`.`id` WHERE `user`.`id`=27 LIMIT 20

I need to get "realties.realtyname" but it keep showing "not set". The SQL is returning results in the realtyname column.

Thanks for any help

2

I display relational data very often. Suppose I have a table user and a table school and user.school_id => school.school_id and school also has school_name column.

In my Model I should have a method getSchool()… that has a relation defined…

Now in my view files…

If I want to change only the data being displayed I use

'school.school_name'

If I want to change the field name displayed I then use

['attribute'=&gt;'Some Name',''value'=&gt;'school.school_name'],

Based on this, I would try…

['attribute' =&gt; 'Realty Name', 'value' =&gt; 'realties.name'],

since the column name in realties table is "name"

3

Because my property and realty both shares the same column name "name", I used "alias" to differentiate it :




 'query' => Property::find()

                    ->joinWith(['owner','realties'])

                    ->where(['user.id'=>$id])   


                    ->select('property.*, property.name as propertyname, company.name as realtyname')

How to display the Alias name in GridView? These are not working :




      <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'columns' => [

            ['class' => 'yii\grid\SerialColumn'],


            'name',   <--- show property name


            ['attribute' => 'Property Name', 'value' => 'propertyname'],  <--- gets error unknown property


            'realties.realtyname',   <--- no data shown


            ['attribute' => 'Realty Name', 'value' => 'realties.realtyname'],   <-- no data shown




            ['class' => 'yii\grid\ActionColumn'],

        ],

    ]); ?>
4

I found the problem why there is no data…




    public function getRealties()

    {

        return $this->hasMany(Company::className(), ['id' => 'realty_id'])->viaTable('property_realty_assignment', ['property_id' => 'id']);  <---- REPLACE THIS


        return $this->hasOne(Company::className(), ['id' => 'realty_id'])->viaTable('property_realty_assignment', ['property_id' => 'id']);  <----- WITH THIS




    }

Thanks