Rails 3. How to find by calculated virtual attribute?

Question

I have a dynamically calculated virtual attribute called estimated_time_of_arrival...

shipment.rb
def estimated_time_of_arrival
  if self.etd_origin.nil?
    "N/A: Please enter ETD Origin to calculate ETA Place of Delivery"
  else
    if self.mode == "Air"
      self.etd_origin + 7.days
    else
      self.etd_origin + (Place.find_by_city(self.place_of_loading).transit_time).days
    end
  end
end

I need to calculate the late shipments like this...

Shipment.where('estimated_time_of_arrival < ?', Date.today)

But that would produce an error "no such column estimated_time_of_arrival"

How could I make a query like that work?

----UPDATE----

Ok, so I added a estimated_transit_time column to the shipments table so I won't have to be looking at the transit_time on the places column. So the function would now be...

shipment.rb
def estimated_time_of_arrival
  if self.etd_origin.nil?
    "N/A: Please enter ETD Origin to calculate ETA Place of Delivery"
  else
    if self.mode == "Air"
      self.etd_origin + 7.days
    else
      self.etd_origin + (self.estimated_transit_time).days
    end
  end
end

and I tried this

AirTransitDays = 7
  scope :late, lambda {
    where( %"( etd_origin +
      INTERVAL ( CASE mode
        WHEN 'Air' THEN ?
        ELSE estimated_transit_time
        END
      ) DAYS
    ) < CURRENT_DATE
   ",
   AirTransitDays)
  }

and get error

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "DAYS": syntax error: SELECT "shipments".* FROM "shipments"  WHERE (( etd_origin +
   INTERVAL ( CASE mode
     WHEN 'Air' THEN 7
     ELSE estimated_transit_time
     END
   ) DAYS
 ) < CURRENT_DATE
) ORDER BY file_number

Answer 1

Something like this ought to be equivalent:

Shipment.
  # This JOIN may be slow with many records if `places.city` isn't
  # indexed--see "Bonus" below
  joins('JOIN places ON shipments.place_of_loading = places.city').
  where %"( etd_origin +
            INTERVAL ( CASE mode
                         WHEN 'Air' THEN 7
                         ELSE places.transit_time
                        END
            ) DAYS
         ) < CURRENT_DATE
  "

You could instead do this in a scope, which is a bit more "Railsy" (and highly recommended):

class Shipment < ActiveRecord::Base
  AirTransitDays = 7

  scope :late, lambda {
    joins( 'JOIN places ON shipments.place_of_loading = places.city' ).
    where( %"( etd_origin +
               INTERVAL ( CASE mode
                            WHEN 'Air' THEN ?
                            ELSE places.transit_time
                          END
               ) DAYS
             ) < CURRENT_DATE
      ",
      AirTransitDays
    )
  }

end

# Usage:
Shipment.late.all # => [ #<Shipment id:...>, $<Shipment id:...>, ... ]

Bonus

Does places have an id key? Rather than having shipments.place_of_loading correspond to places.city you should have shipments.place_of_loading_id correspond to places.id, which would allow you to have models like the following:

class Place < ActiveRecord::Base
  has_many :shipments
end

class Shipment < ActiveRecord::Base
  AirTransitDays = 7

  belongs_to :place_of_loading, :class_name => 'Shipment'

  scope :late, lambda {|origin|
    joins( :place_of_loading ).  # <= See? Simpler.
    where( ... )
  }

end

In addition to making the scope simpler, it will let you do things like:

@shipment = Shipment.include( :place_of_loading ).where(...).first
@shipment.place_of_loading.city  # => Chicago, IL

Just a thought. ;)

Answer 2

Building on Jordans answer: add a class method to your model

def self.will_arrive_within(days)
  where(' ... nifty sql here ... ')
end

now all your controllers and other models can just use

Shipment.will_arrive_within(7)

and nobody has to know about how you implemented this.

see the railscast 215: Advanced Queries in Rails 3