CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
nate17
nate17

Reputation: 31

How to get 2 or more records per unique column value in ActiveRecord?

How to get 2 or more records per unique column value in ActiveRecord?

Given:

Name     | School
Jonathan | Sunshine College

Dylan    | Sunshine College
Matt     | Sunshine College
Joseph   | Sunshine College
Stephen  | Greenville School
Phil     | Greenville School
Warren   | Greenville School


JohnPaul | Greenville School

then if I wanted to output 2 records per school, it will look like this:

Name     | School
Jonathan | Sunshine College

Dylan    | Sunshine College
Stephen  | Greenville School
Phil     | Greenville School

If I wanted 3 records per school then it'll look like this:

Name     | School
Jonathan | Sunshine College

Dylan    | Sunshine College
Matt     | Sunshine College
Stephen  | Greenville School
Phil     | Greenville School
Warren   | Greenville School



Upvotes: 2

Views: 63

Answers (3)

peter
peter

Reputation: 42192

Here a way without using sql.

Student.group(:school)
   .each{|student| Student.where(school: student.school)
   .first(2)
   .each{|result| puts "#{result.name.ljust(20, ' ')}| #{result.school}"}}

# Phil                | Greenville School
# Warren              | Greenville School
# Jonathan            | Sunshine College
# Dylan               | Sunshine College

Upvotes: 1

Sebastián Palma
Sebastián Palma

Reputation: 33420

You can partition the data of the table by the column value you need. After that you can use a condition to tell how many records per partition you want:

Student
  .from(
    Student.select('*, row_number() OVER (PARTITION BY school) AS rownum')
  )
  .select('*')
  .where('rownum < 2')

Upvotes: 4

Kris
Kris

Reputation: 19948

You could uniq when you have transformed the collections of models in to a list of attributes which can be compared for equality:

MyModel.all.pluck(:first_name, :last_name).uniq

Upvotes: 0

Related Questions