Rails scope based on range of time of associated objects

user_634401 0
Viewed 78

I am attempting to create a performant scope that looks at a model's associated record's starts_at datetime, and fetch objects whose associated records fall within that window.

For example:

class Championship < ApplicationRecord
  has_many :races
end

class Race < ApplicationRecord
  belongs_to :championship
end

Each Race record has a starts_at timestamp. I need to look at each Championship's Races, order those Races by starts_at, fetch the first one (chronologically) and check if we are PAST that time, and fetch the last one and check if we are PRIOR to that time.

class Championship < ApplicationRecord
  scope :active, -> { joins(:races)...? }

But I'm really not sure how to order these records in SQL and fetch the first or last record for evaluation.

UPDATE (for clarification)

c = Championship.create()
r1 = c.races.create(starts_at: '2021-05-01')
r2 = c.races.create(starts_at: '2021-06-01')
r3 = c.races.create(starts_at: '2021-07-01')
r4 = c.races.create(starts_at: '2021-08-01')

So let's say the current time is:

  • 04/01/21: Championship should not be active
  • 05/01/21: Championship should be be active
  • 05/15/21: Championship should be be active
  • 08/15/21: Championship should not be active

UPDATE (resolution) Here's what we ended up with:

scope :active, -> { where(id: Race.select(:championship_id).group(:championship_id).having("MIN(races.starts_at) - interval '1 day' < ?", Time.now).having("MAX(races.starts_at) + interval '2 days' > ?", Time.now).pluck(:championship_id)) }
ruby-on-rails sql
1 Answers

No need to order your table!

class Championship < ApplicationRecord
  def self.active
    condition = Races
      .select(:championship_id)
      .group(:championship_id)
      .having('min(races.starts_at) <= ?', date)
      .having('max(races.starts_at) >= ?', date)
    joins(:races).merge(condition)
  end

  (...)
end

PS: if you have a performance issue you can add an index on this column with rails generate migration add_index_to_races_starts_at. It should generate the following migration:

class AddIndexToRacesStartsAt < ActiveRecord::Migration[6.1]
  def change
    add_index :products, :part_number
  end
end
user_164210350
Related