Sql select columns from different tables

Questions Components & MVC
1

I want to select roles.rolename using Sql Abstraction, but i get users.roles.role_name.

CODE:

        $sql = new Sql($adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'users.user_id',
            'roles.role_name',
        ]);
        $select->join('role_user', 'users.user_id = role_user.user_id', '*', $select::JOIN_LEFT);
        $select->join('roles', 'roles.role_id = roles_user.role_id', '*', $select::JOIN_LEFT);
        $select->where(['user_id' => $userId]);

SQL OUTPUT:

SELECT users.users.user_id AS users.user_id, users.roles.role_name AS roles.role_name, role_user.,roles. FROM users LEFT JOIN role_user ON users.user_id = role_user.user_id LEFT JOIN roles ON roles.role_id = roles_user.role_id WHERE user_id = ‘d20e7d44-f0eb-11ea-8ea5-aa1adea2f987’

Where is the problem ?

2 
        $sql = new Sql($adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'user_id',
        ]);
        $select->join('role_user', 'users.user_id = role_user.user_id', [], $select::JOIN_LEFT);
        $select->join('roles', 'roles.role_id = roles_user.role_id', ['role_name'], $select::JOIN_LEFT);
        $select->where(['user_id' => $userId]);

3 params - field for select from join table

1 Like