CODEWITHSUNDEEP
phpdatabasemany-to-manyyii
Lefsler
Lefsler

Reputation: 1768

Many to many relationship on advanced search

I created 3 tables:

User
User-Group
Group

Where I can have a many-to-many relationship.

But how I create on the search method a find to it? How can I get all users that have a specific group, like

select u.* from users as u, user-group as ug, group as g
where g.name = "group_1" and ug.group_id = g.id and ug.user_id = u.id ?

My code so far:

    public function relations()
    {
            // NOTE: you may need to adjust the relation name and the related
            // class name for the relations automatically generated below.
            return array(
                    'group'=>array(self::MANY_MANY, 'Group', 'tbl_profile(id_user, id_group)'),
                    'groupList' => array( self::HAS_MANY, 'Group', 'id_user' ),

    }

    $criteria->with=array('groupList' => array(
                    'condition' => 'id_user = 1',
            ));

Upvotes: 0

Views: 1320

Answers (3)

acorncom
acorncom

Reputation: 5955

I would start with the steps in this wiki article: http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

Then, you'd need to add your join table groupID column to your gridviewand user search criterias. You will probably also need to add a with and together=true line to your search criteria.

Note that he has you add a new model attribute to your model to hold the search criteria. If you are using a drop down filter, you can specify your join table attributes' name directly.

Upvotes: 1

Onur Eren Elibol
Onur Eren Elibol

Reputation: 341

User::model()->with(array(
   'group'=>array(
      'alias'=>'g',
      'condition'=>'g.name=:gName',
      'params'=>array(':gName'=>'group_1')
   )
))->findAll();

or 

$crit = new CDbCriteria();
$crit->alias = 'g';
$crit->addColumnCondition(array('g.name'=>'group_1'));
User::model()->with(array('group'=>$crit))->findAll();

or

$crit = new CDbCriteria();
$crit->with = array(
       'group'=>array(
          'alias'=>'g',
          'condition'=>'g.name=:gName',
          'params'=>array(':gName'=>'group_1')
       )
    );
User::model()->findAll($crit);

or

$crit1 = new CDbCriteria();
$crit1->alias = 'g';
$crit1->addColumnCondition(array('g.name'=>'group_1'));
$crit2 = new CDbCriteria();
$crit2->with = array('group'=>$crit1);
User::model()->findAll($crit2);

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

How i get all users that have a specific group?

select u.* from group g
join user-group ug on g.id = ug.group_id
join users u on u.id = ug.user_id
where g.name = "group_1"

Upvotes: 0

Related Questions