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
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?
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 */
),
)
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)'),
);
}
So my example would change into following
$model=User::model()->with('groups')->together()->findAll($criteria)
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.
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?