Yii Framework Forum: MANY_MANY, how to select some fields from the link table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

MANY_MANY, how to select some fields from the link table Rate Topic: -----

#1 User is offline   jptsetung 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 04-March 10

Posted 18 March 2010 - 10:36 AM

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 ?
0

#2 User is offline   lgoss007 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 88
  • Joined: 01-October 09

Posted 19 March 2010 - 10:56 AM

View Postjptsetung, 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.
0

#3 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 20 March 2010 - 09:38 AM

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.
0

#4 User is offline   yiqing95 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 599
  • Joined: 27-December 10
  • Location:china

Posted 13 July 2011 - 07:25 AM

View PostMike, 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 ::)
0

#5 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 13 July 2011 - 10:00 AM

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
0

#6 User is offline   Ben. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-January 11

Posted 30 July 2011 - 05:07 PM

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.
0

#7 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 30 July 2011 - 05:18 PM

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

0

#8 User is offline   Ben. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-January 11

Posted 30 July 2011 - 05:28 PM

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.
0

#9 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 31 July 2011 - 07:40 AM

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.
0

#10 User is offline   Ben. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-January 11

Posted 31 July 2011 - 08:05 AM

Now it works, thanks for your great help!
0

#11 User is offline   organium 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 09-January 12

Posted 15 March 2012 - 01:30 AM

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.
0

#12 User is offline   manuel-84 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 91
  • Joined: 06-December 11
  • Location:Sardinia

Posted 26 March 2012 - 03:50 PM

this don't work for me

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

View Postorganium, 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.

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users