Returning a list of values in column based on an association table

August 19, 2024

Schema:

Users
- id
- name

Users_In_Company
-company_id
-user_id

Companies
-id
-name

I have to return a list of users, and 1 column should have a list of all the companies they are associated too.

SELECT u.*
FROM users u

Output:

ID   NAME   Companies
1    John   Acme1, Amazon, Walmart

Is a lateral join what can be used here?

Note:
This is for a search results query, so I need to keep performance in mind. I may return up to 1000 rows per request.

>Solution :

Yes, you can use a LATERAL join to achieve the result.

To get it we need to aggregate the list of company names for each user and then return it as part of result.

SELECT 
    u.id, 
    u.name, 
    COALESCE(c.companies, '') AS companies
FROM 
    users u
LEFT JOIN LATERAL (
    SELECT 
        string_agg(c.name, ', ') AS companies
    FROM 
        users_in_company uic
    JOIN 
        companies c ON uic.company_id = c.id
    WHERE 
        uic.user_id = u.id
) c;