How to build a list of unique values and display an array of their ids?

Question

This code is a simplified version of the concept I'm trying to achieve. In reality I'm working with the results of a somewhat complex query - but I figured this simple example would be enough.

I'm trying to output an ordered list of unique values with an array of their id's. I'm unsure if I need to use one or two select statement, or if I should use one select statement and some ruby code.

The data (people)

id: first: last:

1   Julie  Brown

2   Julie  Brown

3   Mike   Smith

4   Julie  Brown

5   Andrea Smith

6   Mike   Smith

7   Jim    Brown

Desired output

Jim Brown (7)

Julie Brown (1, 2, 4)

Andrea Smith (5)

Mike Smith (3, 6)

Answer 1

@people = Person
  .select(:first, :last, "array_agg(id) AS ids")
  .group(:first, :last)

You could use the array_agg function to get all the ids in a single query.

@people.each do |person|
  full_name = [person.first, person.last].join(" ")
  puts "#{ full_name } (#{ person.ids.join(", ") })"
end

How to build a list of unique values and display an array of their ids?

Sign up