string_agg () is not working for Postgres sql when joining on multiple tables-CodePudding

I have to join user_name from user table where as first_name and last_names from user_profile table. Until here everything is fine, but when I try to fetch respective roles assigned from user_role tables it gives multiple rows for single user as 1 user can have multiple roles.

While trying to apply string_agg on role.names (so that multiple roles shown comma separated in single tuple), it gives each role in separate row.

Here is example query I am trying to run in postgresql:

 SELECT users.user_name, user_profiles.first_name, user_profiles.last_name,
(
    SELECT string_agg (roles.name, ',') 
    from roles 
    where roles.id in (
        select user_roles.role_id where users.id = user_roles.user_id
    )
) as name
FROM users 
JOIN user_profiles ON users.id = user_profiles.user_id
JOIN user_roles    ON user_roles.user_id = users.id

CodePudding user response：

You must use GROUP BY clause in order to aggregate more than one record together within a group. That along with the unnecessary (I believe) nested SQL is leading you to wrong results.

Instead consider the following:

SELECT users.user_name, user_profiles.first_name, user_profiles.last_name,
    string_agg (roles.name, ',') as name
FROM users 
  JOIN user_profiles 
    ON users.id = user_profiles.user_id
  JOIN user_roles
    ON user_roles.user_id = users.id
  JOIN roles ON user_roles.role_id = roles.id
GROUP BY users.user_name, user_profiles.first_name, user_profiles.last_name

CodePudding user response：

You forgot a from in your innermost subquery:

SELECT  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        (SELECT string_agg (roles.name, ', ') 
         from  roles 
         where roles.id 
         in (   select u.role_id 
                from user_roles u --this was missing
                where users.id = u.user_id)) as name
FROM users 
    JOIN user_profiles 
        ON users.id = user_profiles.user_id
--  JOIN user_roles --you're already getting roles in the innermost query
--      ON user_roles.user_id = users.id;

without it, that subquery was just selecting one, current row's role_id, instead independently fetching of all roles for the user as you could've expected it to. See this demo.

Note that once this is fixed, you also need to get rid of the last join (commented out in my example) to avoid multiplying users for each of their roles - you're already getting their roles in the innermost subquery anyways.

It would be easier to use a regular group by:

select  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        string_agg(roles.name,', ')
from users 
    inner join user_profiles 
        on users.id=user_profiles.user_id
    inner join user_roles 
        on user_roles.user_id=users.id
    inner join roles 
        on roles.id=user_roles.role_id
group by 1,2,3;