query is returning double/wrong data

Yii 2.0 General Discussions
1

Working on a query thats get data from 1 main table, matches with a other table, and get some extra data from a third table. I have something wrong in my query as it returns the wrong data.

Table : ‘product’ is used to check if the product is active(is_deleted & is_enabled) and we use the ‘category_id’ to match with the ‘category_translation’ table

Table : ‘product_translation’ is the main table where we get data from the language & translation columns.

Table : ‘category_translation’ is used to add a extra column to our data ‘translation’ (alias -> category).

Data should match with ‘attribute’, ‘language’.




       ----------------------------------------------

       | product                                    |

       ----------------------------------------------

       | id | is_deleted | is_enabeld | category_id |

       ----------------------------------------------


       ---------------------------------------------------

       | product_translation                             |

       ---------------------------------------------------

       | product_id | attribute | language | translation |

       ---------------------------------------------------


       ----------------------------------------------------

       | category_translation                             |

       ----------------------------------------------------

       | category_id | attribute | language | translation |                                   

       ----------------------------------------------------



        $query = new Query;


        $slugs = $query->select('a.language , a.translation , a.product_id , b.category_id , c.translation as category')

                       ->from(['a' => 'product_translation'])

                       ->leftJoin(['b' => 'product'] , 'b.id = a.product_id')

                       ->leftJoin(['c' => 'category_translation'] , 'c.category_id = b.category_id')

                       ->where(['b.is_deleted' => 0,

                                'b.is_enabled' => 1,

                                'a.attribute'  => 'slug',

                                'c.attribute'  => 'slug'


                                // when adding this it will not return any data at all

                                // when not adding this it will return double/wrong data

                               'c.language' => 'a.language'


                               ])

                       ->all();
2

When is change this


'c.language' => 'a.language'

to

'c.language' => 'en'

The data comes out, still not correct

3

Note that “‘c.language’ => ‘a.language’” compares the value of ‘c.language’ column with a literal string of ‘a.language’. It doesn’t compare the value of ‘c.language’ column with that of ‘a.language’ column.

I think you may do it like this:




$attr = 'something';

$lang = 'en';


$slugs = $query->select('a.language , a.translation , a.product_id , b.category_id , c.translation as category')

    ->from(['a' => 'product_translation'])

    ->leftJoin(['b' => 'product'] , 'b.id = a.product_id')

    ->leftJoin(['c' => 'category_translation'] , 'c.category_id = b.category_id')

    ->where(['b.is_deleted' => 0,

        'b.is_enabled' => 1,

        'a.attribute'  => $attr,

        'c.attribute'  => $attr,

        'c.language' => $lang,

        'a.language' => $lang,

     ])

     ->all();

Or,




$attr = 'something';

$lang = 'en';


$slugs = $query->select('a.language , a.translation , a.product_id , b.category_id , c.translation as category')

    ->from(['a' => 'product_translation'])

    ->leftJoin(['b' => 'product'] , 'b.id = a.product_id')

    ->leftJoin(['c' => 'category_translation'] , 'c.category_id = b.category_id AND

         c.attribute = a.attribute AND

         c.language = a.language')

    ->where(['b.is_deleted' => 0,

        'b.is_enabled' => 1,

        'a.attribute'  => $attr,

        'a.language' => $lang,

     ])

     ->all();

And, you would be OK with ‘one()’ instead of ‘all()’ for this. ;)

4

Yes this will work…thank you so much!!