What is the best way to use PIVOT? (AR)


I have this classic table setup, where a product can have multiple types:

++++++++++++             ++++++++++++++++++              +++++++++

+ products +             + products_types +              + types +

++++++++++++             ++++++++++++++++++              +++++++++

+ id       +1-----------o+ productID      +          ---1+ id    +

+ name     +             + typeID         +o--------/    + name  +

+ ...      +             ++++++++++++++++++              + ...   +

++++++++++++                                             +++++++++

Is there a Yii way (hopefully more efficient than mine) to query these, rather than writing the SQL myself?

At the end, I want the types in one column separated by commas (ie: group_concat, which might be specific to MySQL)



It is a common many_many relation. In the model rules, you should define the table inbetween as the foreign key. See blog demo for an example.

Thanks, I’ll check it out.

I know, I jumped right into real coding without finishing the proper Yii training :( But it was so easy to do :)



Change of plans …

Is there way (other than hand-knitted-SQL) to create the list individually?

The table structure above still stands. I need to list the products/types separately (i hope it makes sense)

So let’s say I have toothbrushes in [color="#0000FF"]blue[/color] and black => 2 separate record:


+ name       + type  + 


+ toothbrush + black +

+ toothbrush + blue  + 




In order to have this I’d create a model for products_types table as well. Returning all rows with proper relations will give you the list you’re looking for.