Hi all,
I’m stuck on something I thought to be easy…
I want to extend the search function of my user model to find users by their assigned roles.
In the extended search I have added a checkbox list with all roles, and i want the search function to add these to the search criteria. Roles are stored in table "authitem", role assignments in table "authassignment".
I have the relation "authitems":
'authitems' => array(self::MANY_MANY, 'AuthItem', 'authassignment(userid, itemname)')
which gets all assigned roles for each user.
In the search function I tried
$criteria->with = array('authitems', array('select'=>'itemname', 'where'=>'userid = '.$this->id));
$criteria->compare('authassignment.itemname', $this->authitems, true, 'OR');
but this doesn’t work. I get this SQL error message:
Column not found: 1054 Unknown column 'authassignment.itemname' in 'where clause'.
The SQL statement executed was:
SELECT COUNT(DISTINCT `t`.`id`) FROM `user_basic` `t`
LEFT OUTER JOIN `authassignment` `authitems_authitems` ON (`t`.`id`=`authitems_authitems`.`userid`)
LEFT OUTER JOIN `authitem` `authitems` ON (`authitems`.`name`=`authitems_authitems`.`itemname`)
WHERE (authassignment.itemname IS NULL)
…which I don’t understand fully.
Any ideas where I took a wrong turn? Or is there a better approach to achieve what I need?
Thanks!