-1

i have a 3 table

  1. product_tbl // hold product data

  2. specification_tbl // hold all type of specification that my product can have

  3. product_specification_tbl // (kind of Junction table)hold each Product Specification Value based on [$name => $value]format. relation between product_tbl and product_specification_tbl is 1:n

    product-id specification-id name value
    1 10 Tire Width 250
    1 11 Aspect Ratio 50
    1 12 Rim Size 16
    2 10 Tire Width 195
    2 11 Aspect Ratio 70
    2 12 Rim Size 14

now i need combine this specification as follow:

| product_id | [Tire Width]/[Aspect Ratio]R[Rim Size] |
|            |              as Tire Size              |
|------------|----------------------------------------|
|     1      |               250/50R16                |
|     2      |               195/70R14                |

so now i have need to have new column in gridView with ability of sorting based on new combined value (Tire Size Column)

problem is that sorting generally perform on the column but here i need to Sort my products based on a specification value (tire width, aspect ratio, rim size and ... )that is stored in database junction table rows not column.

i think i should first create temporary table that holds specifications indexed by product id and store each data in separate column. or i don't know maybe there is another way?!?

either way i don't know how to do it!!!

i will be glad if someone can help me on this topic. thank you.

<---- GridView 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',
                ],
            ]);
            ?>

<---- Relation Code -------------------------------------------------->

<?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]);
    }
?>

now new problem rise :D i can sort separatley by Tread Width | Aspect Ratio | Diameter

but i need all this 3 column sort ASC or DESC at the same time.

Database Table: enter image description here

select Query Result :

enter image description here

Column i need perform sort on it:

enter image description here

https://forum.yiiframework.com/t/sort-gridview-with-data-from-1-n-relationship-from-junction-table/134403?u=nareka88

Narek Abedian
  • 1
  • 3
  • Initially paste some code what you've done so far so we can help adjust your query. Nobody will write this query for you. – Serghei Leonenco Sep 17 '22 at 17:28
  • @SergheiLeonenco hi, i just wanna know is there any way to do it or not? and i upload some database tables, GridView and wanted query result images. Thank you. – Narek Abedian Sep 18 '22 at 13:00
  • Have you try to make a raw sql query to get desired result? Posting photos is not a good way to communicate here. There is a `code sample` button in the edit area which allow you to paste a sample code. – Serghei Leonenco Sep 18 '22 at 14:44
  • Because you are going to use it in grid view, you will always sort based on only one column. So, it might be better to construct join with required rows from `product_specification_tbl` based on parameter used for sorting instead of building some complex generic query to handle all possible sortings. Something like `... LEFT JOIN product_specifcation_tbl order ON (product.id = order.product_id AND order.specification_id = ) ... ORDER BY order.value ASC`. – Michal Hynčica Sep 18 '22 at 20:25
  • As for combined values, you can do multiple joins and then sort by multiple columns like this: `LEFT JOIN product_specifcation_tbl order1 ON (product.id = order1.product_id AND order1.specification_id = ) LEFT JOIN product_specification_tbl order2 (product.id = order2.product_id AND order2.specification_id = ) ... ORDER BY order1.value ASC, order2.value ASC ...` – Michal Hynčica Sep 18 '22 at 20:30

1 Answers1

-1

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.

Narek Abedian
  • 1
  • 3