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?