CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlactiverecord
Joel Grannas
Joel Grannas

Reputation: 2016

Rails with postgres - activerecord query: sort by column ASC, then group by column

I have a model Laps, which belongs to :Car

So each car has many laps, but I need to do a query where i pull in the top 10 fastest Laps, but only pull in the fastest lap per each car. In other words, I do not want one car to be allowed to have 5 of the top 10 fastest laps in the list.

Each lap has a :car_id field. I want to group on this column, and then pull out the lap with the min(Lap.time) row from the grouping. (AKA fastest lap time from that unique :car_id).

So with Postgres, what is my best approach to this? Can I order all laps first, then group, and then pull the first from the group. Or will grouping not keep the sort order?

Here is my schema for the Lap Model:

  create_table "laps", force: true do |t|
    t.decimal  "time"
    t.string   "video_url"
    t.integer  "car_id"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "user_id"
    t.boolean  "approved"
  end

Do I have to use two combined queries for this?

I can get a unique car id's lap by doing this:

select('DISTINCT ON (car_id) *')

but, I need to order the laps so that it gets the min(lap.time) per that car_id. So when i throw on an order like this:

select('DISTINCT ON (car_id) *').order('car_id, time ASC').sort_by! {|ts| ts.time}

This works, but seems like an odd way to go about it. As soon as i try and change around the order, like removing the car_id from the order, i get postgres errors.

Upvotes: 1

Views: 2315

Answers (2)

PinnyM
PinnyM

Reputation: 35533

As you're discovering, DISTINCT ON isn't going to work for you here because it doesn't match the first term you want to sort on (time). You'll need to use a GROUP BY:

Lap.group(:car_id).limit(10).minimum(:time)

Alternatively, you can make a windowed subquery - but that is quite a bit messier to build. If you need the actual lap information aside for the time, you may have to go that route:

subquery = Lap.select('lap.*, ROW_NUMBER() OVER ( PARTITION BY car_id ORDER BY time, car_id ) as rowNum').to_sql
Lap.scoped.from(Arel.sql("(#{subquery}) fast_laps"))
   .where('rowNum = 1')
   .order('time, car_id')
   .limit(10)

Upvotes: 4

Joel Grannas
Joel Grannas

Reputation: 2016

This is what I did and its working. If there is a better way to go about these please post your answer:

in my model:

def self.fastest_per_car
    select('DISTINCT ON (car_id) *').order('car_id, time ASC').sort_by! {|ts| ts.time}
end

Upvotes: 0

Related Questions