MANY_MANY, how to select some fields from the link table

Hello,

In the relational Active Record guide ( http://www.yiiframework.com/doc/guide/database.arr ) we have this example :


public function relations()

    {

        return array(

            'categories'=>array(self::MANY_MANY, 'Category',

                'tbl_post_category(post_id, category_id)'),

        );

    }

It works fine but it only select fields from the Category table. What if I want to output also some fields of tbl_post_category ?

If you want to use fields from the tbl_post_category then you can create a relation to the tbl_post_category as well.

Even though it might not be the most elegant solution, i’ve used this approach here:

  • Add properties to your Category model, for values from the connecting table
  • Create a custom method in your target model to fetch the categories

So it could look like:


class Category extends CActiveRecord 

{

    public $position; // Usually empty, except for $post->getCategories();

   ...

In your Post class:


private $_categories;


public function getCategories()

{

    if ($this->_categories===null)

        $this->_categories=Category::model()->findAll(array(

            'select'=>'t.*, pc.position AS position',

            'join'=>"INNER JOIN tbl_post_category pc ON pc.category_id=t.id AND pc.post_id=$this->ID"

        ));

    return $this->_categories;

}

You then fetch the Categories with $post->getCategories(); and can access the $category->position from the connecting table.

this solution is wonderful ! thanks Mike, it helped me timely ::)

Also check out the new ‘through’ feature in ARR. You can build an alternative solution with it (namely by replacing MANY_MANY with 2 HAS_MANY relations. The second one uses ‘through’ on the first one).

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through

Mike, you can give an example?

I defined two relationships in my User_Skill_Rel-Model:




'user'=>array(self::HAS_MANY, 'User', 'user_id'),

'skill'=>array(self::HAS_MANY, 'Skill', 'skill_id'),



Now what can I do with the "through" function? I want to get all Skills of the User which has a quality > 3, assuming quality is an attribute of the relationship.

You need to define the relations in your User model.




'skillRels'=>array(self::HAS_MANY,'UserSkillRel','user_id'),

'skills'=>array(self::HAS_MANY,'Skill','skill_id','through'=>'skillRels'),



I didn’t test this, but i think, you now can specify conditions for your skillRels relation, that will also affect, which skills are pulled in into your result. Something like this should probably work:




User::model()->with(array(

    'skillRels'=>array('condition'=>'skillRels.quality>3'),

    'skills'

))->findByPk($userid);

Thank you for your fast reply.

This is what I have:

User has the relations:


'skillRel'=>array(self::HAS_MANY,'UserSkillRel','user_id'),

		  'skills'=>array(self::HAS_MANY,'Skill','skill_id','through'=>'skillRel'),

Skill and UserSkillRel have no relationships defined.

The tables are called "user", "skill" and "user_skill_rel".

When I run


User::model()->with(array(

		    'skills'=>array('condition'=>'skills.price>3'),

		    'user'

		))->findByPk(1);

it says "Relation "user" is not defined in active record class "User"."

So I removed the “user” in the query. Now I get “Column not found: 1054 Unknown column ‘skills.price’ in ‘where clause’.” But even if I adjust the table name it won’t find it.

What am I doing wrong? Sorry, Im very new to Yii.

I should not post messages so late at night :). Of course the relations in with() should match the name of your relations in your User model. I’ve updated the post above.

Now it works, thanks for your great help!

You could use something like this




public function relations()

    {

        return array(

            'categories'=>array(self::MANY_MANY, 'Category',

                'tbl_post_category(post_id, category_id)', 'select'=>'*, categories_categories.rating'),

        );

    }



Category model should contain


public $rating;

field.

And that is all, now you can use it :




$post = Post::model()->with('categories')->find();

foreach($post ->categories as $category){

     echo $category->rating;

}



Hope it helps.

this don’t work for me

what is, actually, the easyest way to access data in the intermediate table?