Hello everyone, I’m new to Yii and my current problem now is with CDbCriteria and its search function.
I’ve a model, Group , related to a User through a Role and viceversa.
Example: in roles table there are user_id and group_id columns that redirect more users to a Group, and a group to an user.
Group table columns :
-
id
-
nome
User table columns:
-
id
-
username
-
password
-
…
Now there is a table, dati_anagrafici which is related by HAS_ONE relation with users (and viceversa); this table contains all user’s personal data ( such as name,surname ecc ).
dati anagrafici table columns:
-
user_id
-
nome
-
cognome
-
birthday
-
…
class Group extends CActiveRecord{
public function relations()
{
return array(
'roles'=> array(self::HAS_MANY,'Role','group_id'),
'users'=> array(self::HAS_MANY, 'User', array('user_id'=>'id'),
'through' => 'roles')
);
}
class User extends CActiveRecord
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(
'role' => array(self::HAS_ONE, 'Role','user_id'),
'dati_anagrafici' => array(self::HAS_ONE, 'DatiAnagrafici', 'user_id',),
'group' => array(self::HAS_ONE, 'Group', array('group_id'=>'id'), 'through' =>'role')
);
}
I’d like to relate by default users and dati_anagrafici in order to obtain user’s personal data simply requesting the user table. So i set the default scope :
class User extends CActiveRecord
public function defaultScope()
{
return array(
'with' => array("dati_anagrafici" => array(
'alias' => 'dt',
))
);
}
I’d search a group both from its name either from a its user’s name/username:
But it won’t work, because with this code in the GroupController:
class GroupController extends Controller
...
public function actionIndex($string = '')
{
...
$criteria->with = array('users'=>array('alias'=>'u'));
$criteria->addSearchCondition('t.nome',$string,true,'OR');
$criteria->addSearchCondition('dt.nome',$string,true,'OR');
}
$dataProvider = new CActiveDataProvider('Group',
array('criteria'=>$criteria));
...
}
gives me this error:
1052 Unknown column 'dt.nome' in where clause.
The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `groups` `t`
LEFT OUTER JOIN `roles` `roles` ON (`roles`.`group_id`=`t`.`id`)
LEFT OUTER JOIN `users` `u` ON (`roles`.`user_id`=`u`.`id`)
LEFT OUTER JOIN `dati_anagrafici` `dt` ON (`dt`.`user_id`=`u`.`id`)
WHERE ((t.nome LIKE :ycp0) OR (dt.nome LIKE :ycp1))
The same if as second searchCondition i put users.username (also with ` quotes); it gives me the column as unknown.
But if I set only one searchCondition such the name of Group, it works …
What’s my error? Please sorry my english and help me