CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
karlingen
karlingen

Reputation: 14645

Improve scheduling / booking sql query in rails

I'm trying to improve the SQL for the following schedule:

enter image description here

It looks something like this:

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

@users consists of users present in the table.

@dates consists of dates: beginning of week -> end of week

Obviously this code suck because a query is fired for each date, booking and user. Having more than a few users would take a long time for the page to load.

How can I improve this query? Is it even possible to get all this data out of the database just by using a one (big) query? I'm using PostgreSQL if that makes any difference.

One suggestion is that I get all bookings that are between @dates and group by dates. But this means that I will have to replace non-existent bookings for particular date and user with a NULL value but I have no idea how I would be able to do that.

Here is the sql output:

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'

Upvotes: 1

Views: 104

Answers (2)

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42869

# action
@dates = start_date..end_date
@users = User.some_scope.includes(:bookings)

# view
- @users.each do |user|
  %tr
    - @dates.each do |date|
      %td
        = user.bookings.select{|booking| booking.date == date}.each do |user_booking|
          = user_booking

This should only hit the database twice ( 2 queries one for users and one for bookings )

Upvotes: 0

MrYoshiji
MrYoshiji

Reputation: 54882

You can do as following:

# 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

Then in your view:

# 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

Upvotes: 1

Related Questions