Extend Search() Function To Find Users By Roles

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?


Your with has authitems but your compare has authassignment…