sql - 改进 rails 中的调度/预订 sql 查询

Question

我正在尝试为以下计划改进 SQL:

看起来像这样:

- @users.each do |user|
  - @dates.each do |date|
    %td
      - Booking.where(user: user, date: date).each do |booking|
        = booking.shift_time

@users 由表中存在的用户组成。

@dates 由日期组成:一周的开始 -> 一周的结束

显然这段代码很糟糕，因为每个日期、预订和用户都会触发一个查询。拥有多个用户将需要很长时间才能加载页面。

我怎样才能改进这个查询？是否有可能仅通过使用一个(大)查询就从数据库中获取所有这些数据？如果有任何不同，我正在使用 PostgreSQL。

一个建议是我获取所有在 @dates 和按日期分组之间的预订。但这意味着我将不得不用 NULL 值替换特定日期和用户的不存在的预订，但我不知道我将如何做到这一点。

这是sql输出:

SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-09'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-10'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-11'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-12'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-13'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-14'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 1 AND "bookings"."date" = '2015-03-15'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-09'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-10'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-11'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-12'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-13'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-14'
SELECT "bookings".* FROM "bookings" WHERE "bookings"."user_id" = 2 AND "bookings"."date" = '2015-03-15'

Answer 1

您可以进行如下操作:

# controller
def your_action
  @users = User.some_scope
  @dates = some_logic_to_return_array_of_dates
  bookings = Booking.where(user_id: @users.map(&:id), date: @dates)
  # the above will select all Booking record having user_id in the selected users' ids AND date IN the selected range/array of dates
  @bookings_by_date = bookings.group_by(&:date)
  # group results like
  # {
  # <Date> => [<Booking>, <Booking>],
  # <Date2> => [<Booking>]
  # }
end

那么在你看来:

# view
- @users.each do |user|
  - @dates.each do |date|
    %td
      - todays_bookings = @bookings_by_date[date].presence || []
      - todays_bookings.select{ |booking| booking.user == user }.each do |booking|
        = booking.shift_time