Cakephp 3 findAuth join table return array

Question

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

I need to use the query builder to generate the MySQL query I posted above to select 1 row containing a user and multiple roles: [1] [John] [$2$10...] [false] ['admin,'tech','manager'] — James Jayson
you should return query object, do not call ->toArray() on query object — N Nem

James Jayson James Jayson · Accepted Answer · 2017-01-21T15:00:38

What I finally ended up going with is this:

public function findAuth(\Cake\ORM\Query $query, array $options)
{
    $query
    ->select(['id', 'username', 'passwordHash', 'locked'])
    ->join([
        'table' => 'user_roles',
        'conditions' => ['user_roles.user_id = Users.id']])
     ->contain(['UserRoles.Roles'])
    ->join([
        'table' => 'roles',
        'conditions' => ['roles.id = user_roles.role']])            
    ->group(['Users.username']);
    return $query;
}

This gives me a multi-dimensional array of: user_roles[index[id, user_id, roles[id, role]]]

in my case I have 2 entries for the index (0 and 1) and I can check the role of the user with the in_array function within a foreach loop

Cakephp 3 findAuth join table return array

1 Answers