CODEWITHSUNDEEP
ruby-on-railspostgresqljoingroup-byleft-join
Tom Bom
Tom Bom

Reputation: 1721

How to group Left Outer Join

I have two models, User and Telephone. User has many telephones.

I'm making a list on active admin and if user has two phones I would like to group them together. I need a raw sql query, and the one that I wrote gives two separate rows.

What I want:

John Legend 123456, 654321

What I get:

John Legend 123456
John Legend 654321

This is my query:

      a = "SELECT user.id, user.first_name, user.last_name, telephone.id AS telephone_id, telephone.number AS telephone_number,
      FROM users 
      LEFT OUTER JOIN telephones ON telephones.user_id = user.id
      GROUP BY users.id, telephones.user_id, telephones.number, telephones.id
      ORDER BY user.id DESC"

Upvotes: 1

Views: 169

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

If you are using Postgres 9.0+, then you may use the STRING_AGG function here:

SELECT
    u.id,
    u.first_name,
    u.last_name,
    STRING_AGG(t.number, ',') AS telephone_number
FROM users u
LEFT JOIN telephones t
    ON t.user_id = u.id
GROUP BY
    u.id
ORDER BY
    u.id DESC;

I only included the three columns which your expected actually shows, though you may add other columns if you want.

Upvotes: 3

Related Questions