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
Jim Brown (7)
Julie Brown (1, 2, 4)
Andrea Smith (5)
Mike Smith (3, 6)
@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