holala
(Holala)
1
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
(Rohit N Sarpotdar)
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?
aztech
(Tomasz Suchanek)
3
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
(Holala)
4
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
(Tomasz Suchanek)
5
So my example would change into following
$model=User::model()->with('groups')->together()->findAll($criteria)
rohit
(Rohit N Sarpotdar)
6
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.
jap1968
(Josean1968)
7
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?