CODEWITHSUNDEEP
sqlrubyruby-on-rails-4activerecord
Peter Nguyen
Peter Nguyen

Reputation: 359

How to create scope or function to select records in specific hours in specific day?

I have a model Order.rb. I want to select all the orders in specific time range in a specific day. For example: select all orders created from 0-6 hours of yesterday. Or select all orders created from 0-12 hours of the day before yesterday.

Currently, I can create scope to select orders in specific day. Here it is:

scope :in_day, -> (day) { where("orders.created_at >= ?", (-1 * day).days.ago.beginning_of_day)\
      .where("orders.created_at < ?", (-1 * (day + 1)).days.ago.beginning_of_day) }

So I can get all the orders from 2 days before just by:

Order.in_day(-2)

But I don't know how to create a scope like in_hour, so when I need to get all orders from 0-6 hours of 2 days before I simply just need:

Order.in_hour(-2, 6)

Or something like that.

Furthermore, I want to select the replied orders in a specific time range of specific day. The code to get replied orders like this:

Order.joins( "LEFT JOIN messages ON orders.id = messages.order_id").uniq.where( "messages.user_id = orders.receive_user_id")

And I can select all replied orders from 0-12 hours of 1 day before:

Order.where(created_at: (1.days.ago.beginning_of_day.to_time..(1.days.ago.beginning_of_day.to_time + 12.hours))).joins( "LEFT JOIN messages ON orders.id = messages.order_id").uniq.where( "messages.user_id = orders.receive_user_id").count

But still, I don't know how to create a scope for it. Can anyone help?

Upvotes: 0

Views: 80

Answers (1)

Peter Nguyen
Peter Nguyen

Reputation: 359

After hours of trying and errors I get this one for replied orders in hours of specific day:

scope :replied_in_hour, -> (day, hour) { where("orders.created_at >= ?", (-1*day).days.ago.beginning_of_day.to_time)\
      .where("orders.created_at < ?", (-1*day).days.ago.beginning_of_day.to_time + hour.hours)\
      .joins( "LEFT JOIN messages ON orders.id = messages.order_id").uniq.where( "messages.user_id = orders.receive_user_id") }

It seems working like what I want! I answer here for anyone have the same problem like me. Hope it'll help! :)

Upvotes: 1

Related Questions