Group records by date ranges and count them in Rails

Question

I was struggling with an ActiveRecord query but I can accomplish what I want. I have the following models and relationships:

class User < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
  belongs_to :user
  belongs_to :clinic
end

class Clinic < ActiveRecord::Base
  has_many :orders
end

And I need to get the Users placed orders by periods of time. Total orders placed in the last month, last 3 months, last 6 months and last 12 months using the created_at attribute.

What I want to get is something like this:

• As you can see the orders in each period of time is the sum of the previous one so that in the first row the total in the 12 months column is the sum of the previous 3 periods of time plus the left 6 months of the year.
• Users can appear as many times as they have clinics related to and the total for the corresponding columns is related to the given user clinic.
• Also I do need to get date of the very last placed order.

I've been trying some workarounds and the best way to get it was to get first the users list with the corresponding clinics. Then I went through each and every user and perform the following queries to get the total on each period of time:

users = User.joins(:clinics).select('user.id, clinic.name')

users.each do |user|
  month = user.orders.where('created_at > ? and created_at < ?', Time.current.beginning_of_month, Time.current.end_of_month).count
  month_3 = user.orders.where('created_at > ? and created_at < ?', Time.current.beginning_of_month - 2.months, Time.current.end_of_month).count
  month_6 = user.orders.where('created_at > ? and created_at < ?', Time.current.beginning_of_month - 5.months, Time.current.end_of_month).count
  month_12 = user.orders.where('created_at > ? and created_at < ?', Time.current.beginning_of_month - 11.months, Time.current.end_of_month).count
end

I'm wondering if there's a better way to do it without iterating over each and every user record and perform the queries there!

Does anyone have any idea? Thanks in advance.

Answer 1

Untested but should work like the following:

beginning_of_month = Time.current.beginning_of_month
end_of_month = Time.current.end_of_month

# Let's get first all orders count group by user + clinic
users_per_clinic_orders_count_since_beginning_of_month = Order.where('created_at >= ? AND created_at < ?', , beginning_of_month, end_of_month).group(:user_id, :clinic_id).count
users_per_clinic_orders_count_since_three_months_ago = Order.where('created_at >= ? AND created_at < ?', , beginning_of_month - 2.months, end_of_month).group(:user_id, :clinic_id).count
users_per_clinic_orders_count_since_six_months_ago = Order.where('created_at >= ? AND created_at < ?', , beginning_of_month - 5.months, end_of_month).group(:user_id, :clinic_id).count
users_per_clinic_orders_count_since_last_year = Order.where('created_at >= ? AND created_at < ?', , beginning_of_month - 11.months, end_of_month).group(:user_id, :clinic_id).count

# Now Let's get all users JOINED with clinics into memory
users = User.joins(:clinics).select('id', 'clinics.name')

data = {}

# Set data with (key == [user_id, clinic_id]) with the corresponding table columns
# All of this should be fast as all of these objects including user and clinic records are already in memory
# data[[user_id, clinic_id]][0] means "id" column
# data[[user_id, clinic_id]][1] means "clinic name" column
# data[[user_id, clinic_id]][2] means "last order at" column
# data[[user_id, clinic_id]][3] means "orders 1 Month" column
# data[[user_id, clinic_id]][4] means "orders 3 Months" column
# data[[user_id, clinic_id]][5] means "orders 6 Months" column
# data[[user_id, clinic_id]][6] means "orders 12 Months" column

users_per_clinic_orders_count_since_beginning_of_month.each do |(user_id, clinic_id), orders_count_since_beginning_of_month|
  if data[[user_id, clinic_id]].nil?
    user_in_memory = users.detect { |user| user.id == user_id }
    next unless user_in_memory # ignore if this user_id is not part of `users` (joined with clinics)
    clinic_in_memory = user_in_memory.clinic
    next unless clinic_in_memory # ignore if this clinic_id is not part of `users` (joined with clinics)
  end

  data[[user_id, clinic_id]] ||= []
  data[[user_id, clinic_id]][0] ||= user_in_memory.id
  data[[user_id, clinic_id]][1] ||= clinic_in_memory.name
  data[[user_id, clinic_id]][3] = orders_count_since_beginning_of_month
end

users_per_clinic_orders_count_since_three_months_ago.each do |(user_id, clinic_id), orders_count_since_three_months_ago|
  if data[[user_id, clinic_id]].nil?
    user_in_memory = users.detect { |user| user.id == user_id }
    next unless user_in_memory # ignore if this user_id is not part of `users` (joined with clinics)
    clinic_in_memory = user_in_memory.clinic
    next unless clinic_in_memory # ignore if this clinic_id is not part of `users` (joined with clinics)
  end

  data[[user_id, clinic_id]][0] ||= user_in_memory.id
  data[[user_id, clinic_id]][1] ||= clinic_in_memory.name
  data[[user_id, clinic_id]][4] = orders_count_since_three_months_ago
end

users_per_clinic_orders_count_since_six_months_ago.each do |(user_id, clinic_id), orders_count_since_six_months_ago|
  if data[[user_id, clinic_id]].nil?
    user_in_memory = users.detect { |user| user.id == user_id }
    next unless user_in_memory # ignore if this user_id is not part of `users` (joined with clinics)
    clinic_in_memory = user_in_memory.clinic
    next unless clinic_in_memory # ignore if this clinic_id is not part of `users` (joined with clinics)
  end

  data[[user_id, clinic_id]][0] ||= user_in_memory.id
  data[[user_id, clinic_id]][1] ||= clinic_in_memory.name
  data[[user_id, clinic_id]][5] = orders_count_since_six_months_ago
end

users_per_clinic_orders_count_since_last_year.each do |(user_id, clinic_id), orders_count_since_last_year|
  if data[[user_id, clinic_id]].nil?
    user_in_memory = users.detect { |user| user.id == user_id }
    next unless user_in_memory # ignore if this user_id is not part of `users` (joined with clinics)
    clinic_in_memory = user_in_memory.clinic
    next unless clinic_in_memory # ignore if this clinic_id is not part of `users` (joined with clinics)
  end

  data[[user_id, clinic_id]][0] ||= user_in_memory.id
  data[[user_id, clinic_id]][1] ||= clinic_in_memory.name
  data[[user_id, clinic_id]][6] = orders_count_since_last_year
end

# Lastly we need to get the "last order at" to be inserted into each data[[user_id, clinic_id]][2]
# this is the only unnecessarily slower part as this do a N-times more sql per find_by
# (but couldnt think of a quick solution yet to eager load the last order created_at)
# Feel free to change this if you know a faster way
data.each do |(user_id, clinic_id), columns|
  last_order = Order.order(created_at: :desc).find_by(user_id: user_id, clinic_id: clinic_id)
  data[[user_id, clinic_id]][2] = last_order.created_at
end

# sort the table by [user_id, clinic_id] and then puts and show the table
puts data.sort.map do |(user_id, clinic_id), columns]
  columns
end
# => [
#   [1, Providence, 2019-08-14T15:51:12.342Z, 2, 6, 12, 24],
#   [1, Joseph Center, 2019-08-22T16:26:29.151Z, 1, 3, 6, 12],
#   [2, Eubanks, 2019-08-22T16:26:29.151Z, 1, 4, 8, 18],
#   ...
# ]