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

Yii 2.0 General Discussions
1

Hi.
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

2

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'])
        ->via('productSpecifications');
    // 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?

3

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.

4

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.

5

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??

6

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
7

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

Relation Created in Product Model

<?php
 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

<?php
           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'];
                        }
                    ],                    
                   'name_en',
                   'product_id',
                ],
            ]);
            ?>

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

@samdark

8

image of Sorting Result

sort_result
sort_result1166×681 60.5 KB

9

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
1)GROUPBY
2)GROUP_CONCAT
3)FIND_IN_SET

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