CODEWITHSUNDEEP
phpgridviewyii2
alwaysfortheworld
alwaysfortheworld

Reputation: 1

How to show values in GridView::widget using INNER JOIN

Guys, i'm very new at it, so please hep me. This is my SQL:

SELECT tb1.login, tb2.user FROM tb1 INNER JOIN tb2 ON tb1.login = tb2.user

I dont get how to make it in query, so i could show values in GridView. I did it but it doesnt work at all.

$query = TB1::find()->select(['tb1.login', 'tb2.user'])
            ->innerWith(TB2::tablename(), 'tb1.login = tb2.user');
 
        return new ActiveDataProvider([
            'query' => $query 
        ]);

My GridView

GridView::widget([
                                'dataProvider' => $dataProvider,
                                'columns' => [
                                    ['class' => 'yii\grid\SerialColumn'],
                                    [
                                        'attribute' => 'tb1.login',
                                    ],
                                    [
                                        'attribute' => 'tb2.user',
                                      }
                                    ],
                                ],
                            ]);

After i check my gridview is says that "not set", please help me! Im veeeery new at it, im sorry

Upvotes: 0

Views: 203

Answers (2)

DrBorrow
DrBorrow

Reputation: 958

To help you along a bit more explicitly:

In the tb1 model add the following join function:

public function getTable2(){
   return $this->hasOne(Tb2ModelNameHere::className,['tbl2_id'=>'tbl1_fk_id']);
}

where 'tbl2_id' and 'tbl1_fk_id' are the fields that connect table 2 and 1 respectively

Within the Gridview, you can simply call the join and it handles the query:

GridView::widget([
                                'dataProvider' => $dataProvider,
                                'columns' => [
                                    ['class' => 'yii\grid\SerialColumn'],
                                    'login', // presuming login is an attribute of tbl1
                                    'table2.field_name_here',
                                    // or
                                    [
                                        'attribute' => 'table2.field_name_here',
                                    ],
                                ],
                            ]);

The code above assumes:

  • That the DataProvider is from Table1. The function "getTable2" must be located in the model of the current dataProvider. In other words: the dataProvider is from Table1 and withhin the Table1 model, we have added the function "getTable2"

To call the function "getTable2" we use "table2" in the attribute of the gridview. Yii automatically adds the "get" and automatically capitalises the first letter. Hence "table2.user_name" will call the join function "getTable2" and retrieve the user_name field from Table2

Upvotes: 0

Jiri Semmler
Jiri Semmler

Reputation: 421

The "correct" way how to handle this use case is using relation methods (https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#relational-data)

So you should define a method "hasXXX" (based on your relation) in the TB1 and then you can access it in the GridView using `value' option

[
    'attribute' => 'packageName', // it has to be defined in the model
    'value' => function (Contract $model) {
        return $model->package->name;
    },
]

Yii2 will handle the SQL and everything...

OR

you can just add public property $publicName and $userName in the TB1 model and set the ALIAS in your SQL select(['tb1.login AS loginName', 'tb2.user AS userName']). But I consider it as quick&dirty solution.

Upvotes: 1

Related Questions