I am attempting to implement a role based authentication system using a custom finder.
public function findAuth(\Cake\ORM\Query $query, array $options)
{
$query
->select(['id', 'username', 'passwordHash', 'locked', 'roles.role'])
->group('username')
->join([
'table' => 'user_roles',
'conditions' => ['user_roles.userid = Users.id']])
->join([
'table' => 'roles',
'conditions' => ['roles.id = user_roles.role']])
->toArray()
;
return $query;
}
The resulting mysql query i need is: select users.id, username, passwordHash, locked, group_concat(roles.role) role from users INNER JOIN user_roles on user_roles.userid = users.id INNER JOIN roles on roles.id = user_roles.role group by users.id