I have Model_User which extends ORM, with a $_has_many
relationship with roles:
protected $_has_many = array(
'roles' => array('model'=>'role','through'=>'users_roles'),
);
I have a static function return all active users:
public static function get_all_active_users()
{
return self::factory('user')->where('status', '=', 'active')->find_all();
}
I would like to update the function to return only users of a specific role, based on the following sql:
SELECT `users`.* FROM `users`
INNER JOIN `users_roles` ON `users_roles`.`user_id` = `users`.`id`
INNER JOIN `roles` ON `roles`.`id` = `users_roles`.`role_id` AND `roles`.`identifier` = 'admin'
WHERE `users`.`status` = 'active'
Is there a way to filter the results within the get_all_active_users method or should I create a new method and manually join?
This is working for me:
return self::factory('user')
->join('users_roles', 'INNER')->on('users_roles.user_id', '=', 'user.id')
->join('roles', 'INNER')->on('roles.id', '=', 'users_roles.role_id')
->where(roles.identifier', '=', 'admin')
->where('status', '=', 'active')
->find_all();
Interested to know any alternatives.