ruby-on-railsactiverecordactive-record-query

Rails 4 ActiveRecord scope to query different date field based on whether it exists


I have a VoyageLeg model that has many date fields. I have an instance method as below to get one of the dates based on whether it has been populated:

def calc_arrival_date
  if ada
    ada #return actual date of arrival
  elsif updated_eda
    updated_eda #return updated estimated date of arrival
  else
    eda #return estimated date of arrival
  end
end

I also have a Voyage model that has_many voyage_legs. How would I create a scope on the Voyage model would would reference the calc_arrival_date, or alternatively do the same logic regarding which date to query in the scope?

I.e. I would like to be able to have a scope on the Voyage that would do something like:

scope :archived, -> { joins(:voyage_legs).where("voyage_legs.calc_arrival_date < ?", Time.zone.now) }

Solution

  • I ended up using part of your code @Shabini Rajadas, but combined the date clause inside the logic as a class method on the VoyageLeg model as follows:

    def self.departed
      where("CASE WHEN voyage_legs.add IS NOT NULL THEN
          voyage_legs.add < :now
        WHEN voyage_legs.updated_edd IS NOT NULL THEN
          voyage_legs.updated_edd < :now
        WHEN voyage_legs.add IS NULL and voyage_legs.updated_edd IS NULL THEN
          voyage_legs.edd  < :now END", now: Time.zone.now.to_date)
    end
    

    Then I can filter Voyages like Voyage.joins(:voyage_legs).merge(VoyageLeg.departed)