How to see columns from join in GridView

Yii 2.0 General Discussions
1

I’m scratching my head here. I have a join on a query and when I try to display the data I want in gridview, nothing is shown. What am I doing wrong here?

$query = Tickers::find()
            ->orderBy([
                'ticker' => SORT_ASC
                ])
            ->leftJoin('sec_type', 'sec_type.id = tickers.type_id');

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

In the view:

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        'id',
        'ticker',
        'name',
        'type_id',
        [
            'attribute' => 'type',
            'value' => 'sec_type.type'
        ],
        'sector_id',
        'country_id',
        'visible',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
2

Even doing the join, when you call Tickers::find() from SQL view is like you are doing this: SELECT tickers.* FROM ...

And you can validate that by doing this, right after building your query:

$query->createCommand()->getRawSql();

In order to “fetch” that data you could add the method addSelect() to your query
OR even better: declare a relation between your records:

public function getSecType() {
    return $this->hasOne(SecType::className(), ['id' => 'type_id']);
}

Read more here in Declaring Relations

3

I do have the relations declared, but I’m not sure how I would use them in this context. What would the query look like?

4

You can access that through the Gridview like this:

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        [
            'label' => Yii::t('app', 'Type'),
            'value' => 'secType.type' // Here it will look for the attribute `type` in the relation
        ],
        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

This will fetch the data.

5

Unfortunately, this doesn’t work.

Is there something wrong with my query? Or how I’m accessing it in GridView?

Whether I use “sec_type.type” or “secType.type”, the column returns “not set”.

6

Which of those methods did you use for fetching the data?

7

Well, neither. I’d like to use the declaration but I don’t know what that query would look like.

Would it be another separate query or a part of the query I’ve written?

8

Also, when I try “addSelect()”, it still does not display the type AND eliminates a large number of results.

I tried adding this:

->addSelect('*', 'sec_type.type')
9

When I change the query to the following:

$query = (new Query())
            ->select('*')
            ->from('tickers')
            ->leftJoin('sec_type', 'sec_type.id = tickers.type_id');

The data is displayed properly, but now the buttons in the action column no longer work.

10

Do this solution instead! add that method to the Tickers model and, if you don’t have a SecType model, create one!

After this is done, just change the GridView to look like in this example:

11

Also change your query to just this:

$query = Tickers::find();

Leave the sorting for the dataProvider: read here about ActiveDataProvider

12

This was the issue! The relation was there, but named getType(). It was generated by gii, so I didn’t bother checking it.

Thank you so much!!!

13

Next question…

How do I add filtering on the Type column like the other columns? I’ve added

->andFilterWhere(['like', 'type', $this->secType]);

To the search function, however, the filtering/sorting function does not appear. Any way to get that in there?

14

now you use the leftjoin:

->leftJoin('sec_type', 'sec_type.id = tickers.type_id')
->andFilterWhere(['like', 'sec_type.type', $this->secType]);

If you now some SQL, I recommend you run the following command, so this you can always no what is the SQL command that you are getting:

15

Hmm, just tried this. The SQL command looks good, but still no filtering.

What else should I check for?

16

Please, show me the SQL generated

17

Here it is…

'SELECT `tickers`.* FROM `tickers` LEFT JOIN `sec_type` ON sec_type.id = tickers.type_id ORDER BY `ticker`'

Looking at it, I see it’s only selecting from tickers. The thing is, when I throw in, “addSelect()” to capture the other columns, it eliminates a number of the results…

18

forget the addSelect, probably the attribute $this->secType is empty. In this case the andFilterWhere() wont apply. Check the value for this attribute.

And maybe try changing the andFilterWhere to andWhere.

19

You’re right, it’s empty, but they all are (‘id’, ‘ticker’, etc.). I don’t think that’s where the problem is.

Now, I’m curious to know how GridView processes the ‘filterModel’…

Edit: I think I may know why. The search model is pulling from TickersSearch, which doesn’t have the columns in it from the relationship tables. Question is now, how do I get it to recognize the other columns?

20

add them to your TickersSearch, add as class var on to the rules().

The logic here is: TickersSearch must have all attributes for a search over tickers, this means that it might contain attributes that doesn’t exist in the Tickers model, or it might even not contain attributes that exist in the Tickers model.