Yii2 get column from junction table and use in gridview

Question

Part of my schema is a pictured: enter image description here

The junction table maps which items are in which category, with the "featured" column marking the item as featured in that category.

What I need to do is get a list of all items in a specific category, including the "featured" attribute from the junction table and pass that data for display into a gridview.

I have created a model for the category_item table and tried to implement the solution proposed here but how do I access and pass the data into the gridview?

Category Model

public function getCategoryItems()
{
    return $this->hasMany(CategoryItem::className(), ['category_id' => 'id']);
}

public function getItems()
{
    return $this->hasMany(Item::className(), ['id' => 'item_id'])->viaTable('{{%category_item}}', ['category_id' => 'id']);
}

CategorySearch Model

public function search($params)
{
    $query = Category::find()->with('items')->with('categoryItems');

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

    $this->load($params);

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

    $query->andFilterWhere([
        'id' => $this->id,
        'type' => $this->type,
    ]);

    $query->andFilterWhere(['like', 'title', $this->title])
        ->andFilterWhere(['like', 'description', $this->description]);

    return $dataProvider;
}

Item Model

public function getItemCategories()
{
    return $this->hasMany(CategoryItem::className(), ['item_id' => 'id']);
}

public function getCategories()
{
    return $this->hasMany(Category::className(), ['id' => 'category_id'])->viaTable('{{%category_item}}', ['item_id' => 'id']);
}

Controller

public function actionCategory($id = null)
{
    if($id == null) {
        $dataProvider = new ActiveDataProvider([
            'query' => Category::find()->all(),
        ]);

        return $this->render('category', [
                'dataProvider' => $dataProvider,
        ]);
    } else {

        $category = Category::findOne($id);

        if($category) {
            $searchModel = new CategorySearch();

            $itemsDataProvider = new ActiveDataProvider([
                'query' =>  $searchModel->search(['CategorySearch'=>['id'=>$id]]),
            ]);

            return $this->render('editCategory', [
                'itemsDataProvider' => $itemsDataProvider
                'model' => $category,
            ]);
        } else {
            throw new NotFoundHttpException('The requested page does not exist.');
        }
    }
}

View

<div class="row">
    <div class="col-md-12">
        <h2>Items</h2>
        <?= GridView::widget([
            'dataProvider' => $itemsDataProvider,
            'columns' => [

                'id',
                'name',
                'categoryItems.id',
                [
                    'attribute' => 'items.description',
                    'format' => 'html',
                    'value' => 'description',
                ],
                [
                    'label'=>'Highlights',
                    'format'=>'raw',
                    'value'=>function($data) use ($model) {
                        if(TagFeaturedItem::find()->where(['item_id'=>$data->id, 'tag_id'=>$model->id])->all()) {
                            return Html::a('Remove as Highlighted', ['/highlights/category/'.$model->id.'/remove/'.$data->id], ['class'=>'btn btn-xs btn-danger']);
                        } else {
                            return Html::a('Add as Highlighted',['/highlights/category/'.$model->id.'/add/'.$data->id], ['class'=>'btn btn-xs btn-primary']);
                        }
                    },
                ],
            ],
        ]); ?>
    </div>
</div>

In general the view file is a mix and match from trying to find a solution. The TagFeaturedItem model is from a previous version of the view, see here

Thanks. And the whole thing doesn't causes error? You just want to access the feature flag? I see another class TagFeaturedItem... — robsch
Yes, I can get the category items via the $category->items relation but the featured column is of course missing. — jimmy
Ah, sorry, to hard for me... Wondering why 'categoryItems.id' and 'items.description' works. And why does new ActiveDataProvider(['query' => $searchModel->search(['CategorySearch'=>['id'=>$id]]),]); work. Shoudn't query be a QueryInterface implementation? — robsch
I'd like to start from a clean slate on this one because I myself have been mixed up trying to following all the articles and advice doing the rounds on the internet. Documentation is sparse/non-existent for this type of problem, so right now I'm looking for just a solution, I can throw away any code written up till now and implement something that works. — jimmy

dataskills dataskills · Accepted Answer · 2015-05-26T21:09:37

In my opinion there may be some design flaws in your project that I will not explore; I will zero in on the question of how to get the related data into the gridview.

You can create a custom column in the gridview and then get the related data, you would add something like this to your gridview column array:

    [
            'label'=>'featured',
            'format'=>'raw',
            'value'=>function($data)
            {
                $categories = $data->categories; 

                $content = "";
                foreach($categories as $category)
                    $content .= $category->title."<br/>";

                return $content;
            }
    ],

Note that $data represents the model returned by the data provider, in the above example from your Item Model.

Yii2 get column from junction table and use in gridview

1 Answers