CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlactiverecordruby-on-rails-4
Joel Grannas
Joel Grannas

Reputation: 2016

grouping with a non-primary key in postgres / activerecord

I have a model Lap:

class Lap < ActiveRecord::Base
  belongs_to :car

  def self.by_carmodel(carmodel)
    scoped = joins(:car_model).where(:car_models => {:name => carmodel})
    scoped
  end

  def self.fastest_per_car
    scoped = select("laps.id",:car_id, :time, :mph).group("laps.id", :car_id, :time, :mph).order("time").limit(1)
    scoped
  end
end

I want to only return the fastest lap for each car.

So, I need to group the Laps by the Lap.car_id and then only return the fastest lap time based on that car, which would determined by the column Lap.time

Basically I would like to stack my methods in my controller:

@corvettes = Lap.by_carmodel("Corvette").fastest_per_car

Hopefully that makes sense...

When trying to run just Lap.fastest_per_car I am limiting everything to 1 result, rather than 1 result per each Car.

Another thing I had to do was add "laps.id" as :id was showing up empty in my results as well. If i just select(:id) it was saying ambiguous

Upvotes: 0

Views: 900

Answers (3)

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

polmiro
polmiro

Reputation: 1986

You are using limit which will return you one single value. Not one value per car. To return one car value per lap you just have to join the table and group by a group of columns that will identify one lap (id is the simplest).

Also, you can have a more ActiveRecord friendly friendly with:

class Lap < ActiveRecord::Base
  belongs_to :car

  def self.by_carmodel(carmodel)
    joins(:car_model).where(:car_models => {:name => carmodel})
  end

  def self.fastest_per_car
    joins(:car_model)
      .select("laps.*, MIN(car_models.time) AS min_time")
      .group("laps.id")
      .order("min_time ASC")
  end
end

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52346

I think a decent approach to this would be to add a where clause based on an efficient SQL syntax for returning the single fastest lap.

Something like this correlated subquery ...

select ...
from   laps
where  id = (select   id
             from     laps laps_inner
             where    laps_inner.car_id = laps.car_id
             order by time asc,
                      created_at desc
             limit    1)

It's a little complex because of the need to tie-break on created_at.

The rails scope would just be:

where("laps.id = (select   id
                  from     laps laps_inner
                  where    laps_inner.car_id = laps.car_id
                  order by time asc,
                           created_at desc
                  limit    1)")

An index on car_id would be pretty essential, and if that was a composite index on (car_id, time asc) then so much the better.

Upvotes: 1

Related Questions