Sort gridview with data from 1:n relationship from junction table

I have table Product.
Every Product has Specification.
Every Specification has Value.

This Specification Value Store in (Junction) Table in product_specification_tbl

there is 1:n relation between “product_tbl” and “product_specification_tbl”

for example:
Product with Product_id 200 has many specification and each of that specification has a value.

                      | Specification_id(1)->value = 205         //Tire Width
Product_id(100)--->   | Specification_id(2)->value = 50          // Aspect Ratio
                      | Specification_id(3)->value = 16          // Rim Size

Now i need Create an column with name CarTireSize in GridView that represent Combination of this Specification Value for each Product_id
example for combination : 205/50R16 ‘/’ and ‘R’ is static characters.

now in this new GridView i need to perform SORT on column “CarTireSize” based on this column value.

ill be grateful if somebody can show me a solution

Hi @nareka88, welcome to the forum.

This is very challenging.
There should be various approaches to accomplish your UI design.

I would first try to setup the relations among Product, ProductSpecification, and Specification.

#1 Product has many ProductSpecifications

public function getProductSpecifications()
    return $this->hasMany(ProductSpecification::class, ['product_id' => 'id']);

#2 Product has many Specifications (via junction)

public function getSpecifications()
    return $this->hasMany(Specification::class, ['specification_id' => 'id'])
    // or
    return $this->hasMany(Specification::class, ['specification_id' => 'id'])
        ->viaTable('product_specification', ['product_id' => 'id']); 

These 2 relations above might have already been created by Gii’s Model Generator.

Now, we have to deal with tire width, aspect ratio and rim size.

I believe we can make functions like the following:

#3 Product has one Tire Width Specification

public function getTireWidth()
    return $this->hasOne(ProductSpecification::class,  ['specification_id' => 'id'])
        ->viaTable('product_specification', ['product_id' => 'id'])
        ->andWhere(['type' => Specification::TYPE_TIRE_WIDTH]);
    // assuming 'specification' table has a column named 'type' which
    // indicates what kind of specification it represents.
    // specification ( id, type, value )

I’m not sure if this works as expected or not …
It it’s OK, then we can make similar functions for aspect ratio and rim size, too.
And now we can display these values in a gridview.

Well, and you need to sort. That’s very challenging … I can’t offer you a quick slution at the moment.

BTW, have you already successfully displayed car tire specs in your gridview?
If so, how did you do that?

My second thought:

Relational ActiveRecord might not be a very good way to go in this scenario.
I would probably want to go with plain SQL approach.

For one thing, ActiveRecord is a bit weak in handling table aliases. But you have to use table aliases extensively in this prolem, since tire width, aspect ratio and rim size share the same tables (‘product_specification’ and ‘specification’) and you need to name them differently.

yes i display each one in separate column and display it in a combined way.
so i have a gridView with coulums
TreadWidth | Aspect Ratio | Rim Size

and another gridView with column that have combined data

exp : 205/50R14

i only have problem with sorting them.

i cant name them separately cause there are cell values not column. idk what to do.

is it wise to create another column in product_tbl and save the combined value when product added into database . then i could sort based on that new column??

Ah, yes. I think it’s a good idea and I would do so, too.

Some people might hesitate to do so, since it would de-normalize the db tables and bring on data duplication.
But it would make things quite simple and robust.

1 Like

i can sort each column separately with these codes. BUT a problem arise i describe in the bottom.

Relation Created in Product Model

 public function getProductTreadWidth()
        return $this->hasOne(ProductSpecification::className(), ['product_id' => 'product_id'])->where(['specification_id' => 2]);
    public function getProductAspectRatio()
        return $this->hasOne(ProductSpecification::className(), ['product_id' => 'product_id'])->where(['specification_id' => 3]);
    public function getProductDiameter()
        return $this->hasOne(ProductSpecification::className(), ['product_id' => 'product_id'])->where(['specification_id' => 5]);

Grid View Code

           echo GridView::widget([
                'dataProvider' => $dataProvider,
                'columns' => [
                    ['class' => 'yii\grid\SerialColumn'],

                        'attribute' => 'brand.name_en',
                        'label' => 'Brand'

                        'attribute' => 'family.name_en',
                        'label' => 'Model',
                        'attribute' => 'productTreadWidth.value_en',
                        'label' => Yii::t('app' , 'Tire Width'),
                        'content' => function($model){
                            return $model->productTreadWidth['value_en'];
                        'attribute' => 'productAspectRatio.value_en',
                        'label' => Yii::t('app' , 'Aspect Ratio'),
                        'content' => function($model){
                            return $model->productAspectRatio['value_en'];
                        'attribute' => 'productDiameter.value_en',
                        'label' => Yii::t('app' , 'Diameter'),
                        'content' => function($model){
                            return $model->productDiameter['value_en'];

now a new problem arise
i need sorting perform on 3 column at the same time :wink:

TreadWidth | AspectRatio | Diameter

by clicking on one of them all 3 column sort ASC or DESC


image of Sorting Result

for sorting each value separately the above code will solve the problem.

for sorting all value together after all searches and tests i just find a way.
now i can sort many row value in gridView just by changein dataProvider query

 'query' => Product::find()->select(['Group_concat({{%product_specification}}.value_en SEPARATOR "/") as size ' , '{{%product_specification}}.product_id'])->joinWith(['brand', 'family', $joinTable ], true, 'LEFT JOIN')->where(['availability_id' => 1])->andWhere('FIND_IN_SET(specification_id,"2,3,5")')->groupBy('product_id'),

it solve using

Thanks to all those who read the issue and tried to solve it.