ruby-on-rails - 按日期范围对记录进行分组并在 Rails 中对其进行计数

Question

我一直在努力处理 ActiveRecord 查询，但我可以完成我想要的。我有以下模型和关系:

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

而且我需要让用户按时间段下订单。 上个月、过去3 个月、过去6 个月 和过去12 个月 使用created_at 属性。

我想得到的是这样的:

• 如您所见，每个时间段的订单数是前一个时间段的总和，因此第一行中 12 months 列中的总数是前 3 个时间段的总和时间加上一年中剩下的 6 个月。
• 用户可以出现的次数与他们的诊所相关，相应列的总数与给定的用户诊所相关。
• 此外，我确实需要获取最后下订单的日期。

我一直在尝试一些解决方法，获得它的最佳方法是首先获得具有相应诊所的用户列表。然后我遍历每个用户并执行以下查询以获得每个时间段的总数:

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!

有人知道吗？提前致谢。

Answer 1

未经测试但应该像下面这样工作:

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],
#   ...
# ]