Rails - How to use custom attribute in where?

Question

I have Order model in which I have datetime column start and int columns arriving_dur, drop_off_dur, etc.. which are durations in seconds from start

Then in my model I have

class Order < ApplicationRecord
  def finish_time
    self.start + self.arriving_duration + self.drop_off_duration
  end
  # other  def something_time ... end
end

I want to be able to do this:

Order.where(finish_time: Time.now..(Time.now+2.hours) )

But of course I can't, because there's no such column finish_time. How can I achieve such result?

I've read 4 possible solutions on SA:

• eager load all orders and select it with filter - that would not work well if there were more orders
• have parametrized scope for each time I need but that means soo much code duplication
• have sql function for each time and bind it to model with select() - it's just pain
• somehow use http://api.rubyonrails.org/classes/ActiveRecord/Attributes/ClassMethods.html#method-i-attribute ? But I have no idea how to use it for my case or whether it even solves the problem I have.

Do you have any idea or some 'best practice' how to solve this? Thanks!

Answer 1

You have different options to implement this behaviour.

1. Add an additional finish_time column and update it whenever you update/create your time values. This could be done in rails (with either before_validation or after_save callbacks) or as psql triggers.

   class Order < ApplicationRecord
     before_validation :update_finish_time
   
     private
     def update_finish_time
       self.finish_time = start_time + arriving_duration.seconds + drop_off_duration.seconds
     end
   end
   

   This is especially useful when you need finish_time in many places throughout your app. It has the downside that you need to manage that column with extra code and it stores data you actually already have. The upside is that you can easily create an index on that column should you ever have many orders and need to search on it.

   An option could be to implement the finish-time update as a postgresql trigger instead of in rails. This has the benefit of being independent from your rails application (e.g. when other sources/scripts access your db too) but has the downside of splitting your business logic into many places (ruby code, postgres code).

2. Your second option is adding a virtual column just for your query.

   def orders_within_the_next_2_hours
     finishing_orders = Order.select("*, (start_time + (arriving_duration + drop_off_duration) * interval '1 second') AS finish_time")
     Order.from("(#{finishing_orders.to_sql}) AS orders").where(finish_time: Time.now..(Time.now+2.hours) )
   end
   

   The code above creates the SQL query for finishing_order which is the order table with the additional finish_time column. In the second line we use that finishing_orders SQL as the FROM clause ("cleverly" aliased to orders so rails is happy). This way we can query finish_time as if it was a normal column.

   The SQL is written for relatively old postgresql versions (I guess it works for 9.3+). If you use make_interval instead of multiplying with interval '1 second' the SQL might be a little more readable (but needs newer postgresql version, 9.4+ I think).