[jptsetung]

Hello,

In the relational Active Record guide ( http://www.yiiframew...de/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 ?

[lgoss007]

jptsetung, on 18 March 2010 - 10:36 AM, said:

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.

[Mike]

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.

[yiqing95]

Mike, on 20 March 2010 - 09:38 AM, said:

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

[Mike]

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.yiiframew...ry-with-through

[Ben.]

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.

[Mike]

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);

This post has been edited by Mike: 31 July 2011 - 07:40 AM

[Ben.]

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.

[Mike]

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.

[Ben.]

Now it works, thanks for your great help!

[organium]

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.

[manuel-84]

this don't work for me

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

organium, on 15 March 2012 - 01:30 AM, said:

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.