[holala]

I have 2 models,Group and User,in group,the relation is:

public function relations()
{
   return array(
      'users' => array(self::MANY_MANY, 'User', 'groupuser(group_id, user_id)'),
   );
}

and i know the sql,but i don't know how to use criteria achieve it,please help,thanks!

SELECT *
FROM user AS a
INNER JOIN groupuser AS b ON b.user_id = a.id
AND b.group_id = 2

[rohit]

First create an instance of CDbCriteria

$criteria=new CDbCriteria();

by default select has * means all columns selection so no need to define

$criteria->select

As you have MANY_MANY relationship between User and Group Model.
Following may work

$criteria->condition='group_id:group_id';
$criteria->params=array(':group_id'=>2);
$model=User::model()->with('users')->findAll($criteria)

I think relations() needs modification! In which model you have put this relation?

[aztech]

If you want to have one SQL query don't forget to use method together()
For more information please look at note in guide.

$criteria->condition='group_id:group_id';
$criteria->params=array(':group_id'=>2);
$model=User::model()->with('users')->together()->findAll($criteria)

If you want to see how is you query looking like add following to application configuration

  /* logging feature */
  'components'=>array(
        'log'=>array(
            'class'=>'CLogRouter',
            'routes'=>array(
                array(
                    'class'=>'CFileLogRoute',
                    'levels'=>'error, warning',
                ),
                array(
                    'class'=>'CWebLogRoute',
                ),
            ),
        ),
        'db'=>array(
            'connectionString' => 'mysql:host=localhost;dbname=xxx',
            'username' => 'xxx',
            'password' => 'xxx',
            'charset' => 'utf8',
            'enableParamLogging' => true,  /* turn on logging sql statements */
        ),
    )

[holala]

Thank you all.
but my model User has not relation 'users','users' is in model Group.

there is relation in model User:

public function relations()
{
	return array(
		'groups' => array(self::MANY_MANY, 'Group', 'groupuser(group_id, user_id)'),
	);
}

[aztech]

So my example would change into following

$model=User::model()->with('groups')->together()->findAll($criteria)

[rohit]

holala, on 04 March 2010 - 12:44 AM, said:

Thank you all.
but my model User has not relation 'users','users' is in model Group.

there is relation in model User:

public function relations()
{
	return array(
		'groups' => array(self::MANY_MANY, 'Group', 'groupuser(group_id, user_id)'),
	);
}

See, we are executing model() method on a model(table) from which we need to fetch results. So thus User::model()
with() tells which relation to use. so User needs a relation to Group model, use that name instead. so it will be 'groups'

Thus finally it should become,

$model=User::model()->with('groups')->findAll($criteria)

as aztech suggested.

[josean]

Could I create a DataProvider based on a MANY_MANY relation?

I want to modify the actionIndex() of a controller in order to list elements filtered by a criteria determined by a N:N relation

Following with the example, what If I want to list just the users belonging to a given group?