Faster Database WHERE for Rails Availability Calen

Question

I'm building a Ruby on Rails hostel bed reservation system and I'm looking for a better way to load my availability calendar. The below code works great for showing bed availability (see attached image), but I feel like the multiple WHERE database calls are too taxing on the server and there must be a quicker way to process the data.

Models:

user.rb

bed.rb (user_id, name)

guest.rb (user_id, name, phone, address, email)

reservation.rb (user_id, bed_id, guest_id, date)

Users are hostel owners. They create as many beds as their business has and give them a name (Bed #1, Bed #2, Twin-Attic, Master-2nd Floor, etc).

Each user has many guests, these are people sleeping at their hostel on a specific date.

Using the simple_calendar gem, I get all reservations for the current month and process them to show a weekly view.

Sorting logic:

Step 1. Iterate through current_user's beds for the first day of the week.(simple_calendar supplies each day's reservations)

Step 2. If the bed has a reservation for that day(!= nil), put bed name and the name of the guest for that reservation.

Step 3. If the bed doesn't have a reservation, put bed name and 'Empty.'

Step 4. After completing the first day of week, move to next day and repeat.(simple_calendar takes care of this)

As I said, the code below works, but when a user has 50 beds, it makes 50 WHERE calls for each day, so 350 for a 7-day calendar (and more if it needs to find the guest's name).

Please let me know if you need any more information or explanations. Thanks!

= week_calendar events: @monthly_reservations, attribute: :date do |date, appts|
  - current_user.beds.each do |bed|
    - if bed.reservations.where(date: date).first != nil
      - guest_name = bed.reservations.where(date: date).first.guest.abbreviated_name
        %p{style: "border: 1px solid black; padding: 5px; font-size: 10px;"}
          = "#{bed.name}: #{guest_name}"
    - else
      %p{style: "border: 1px solid black; padding: 5px; font-size: 10px;"}
        = "#{bed.name}: Empty"

CURRENT Rails hostel bed reservation system

EDIT: The ideal calendar layout would look like the image below:

IDEAL Rails bed reservation system

However, the problem is making the bed name line up with the correct bed reservation. If May 2 has only 2 reservations, Bed 1 and Bed 4, the code doesn't know to leave two EMPTYS before inserting Bed 4's appointment last. Make sense?

EDIT #2:

@DavidAldridge, something like this:

Something like this?

reservs = {"[2017-05-01, bed_1]" => "guest_23", "[2017-05-01, bed_2]" => "empty"}

EDIT 3 Here is what the logs look like when loading a hostel with just 5 beds.

Bed Load (0.3ms)  SELECT "beds".* FROM "beds" WHERE "beds"."user_id" = $1  [["user_id", 1]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-02"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-02"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-02"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-02"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-02"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-03"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-03"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-03"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-03"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-03"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-04"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-04"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-04"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-04"]]
  Reservations Load (0.1ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-04"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-05"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-05"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-05"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-05"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-05"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-06"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-06"]]
  Reservations Load (0.1ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-06"]]
  Reservations Load (0.1ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-06"]]
  Reservations Load (0.3ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-06"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-07"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-07"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-07"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-07"]]
  Reservations Load (0.1ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-07"]]
  Reservations Load (0.2ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 1], ["date", "2017-07-08"]]
  Reservations Load (0.6ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 2], ["date", "2017-07-08"]]
  Reservations Load (0.3ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 3], ["date", "2017-07-08"]]
  Reservations Load (0.3ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 4], ["date", "2017-07-08"]]
  Reservations Load (0.3ms)  SELECT  "reservations".* FROM "reservations" WHERE "reservations"."bed_id" = $1 AND "reservations"."date" = $2  ORDER BY "reservations"."id" ASC LIMIT 1  [["bed_id", 5], ["date", "2017-07-08"]]

EDIT 4 Following @DavidAldridge suggestion, I created a hash in the controller and passed it into my view. The results are STELLAR!

Controller code:

@reservations = {}

@monthly_reservations.each do |x|
  @reservations[[x.date,x.bed_id]] = x.guest.try(:name)  
end

And the view code:

= week_calendar events: @monthly_reservations, attribute: :date do |date, appts|
  - current_user.beds.each do |bed|
    - if @reservations[[date, bed.id]] != nil
      %p{style: "border: 1px solid black; padding: 5px; font-size: 10px;"}
        = "#{bed.name}: #{@reservations[[date, kennel.id]]}"
    - else
      %p{style: "border: 1px solid black; padding: 5px; font-size: 10px;"}
        = "#{bed.name}:"

Answer 1

For your view code, I would try:

= week_calendar events: @monthly_reservations, attribute: :date do |date, appts|
  - current_user.beds.each do |bed|
    %p{style: "border: 1px solid black; padding: 5px; font-size: 10px;"}
      = [bed.name, @reservations[[date, bed.id]]].compact.join(":")

Answer 2

What Robbie is describing is capturing the data in bulk to present it in a second pass. You can do this:

@reservations = Reservations.where(bed_id: [ ... ], date: (from..to)).group_by do |r|
  [r.bed_id, r.date]
end

Where this returns a singular structure that should contain all the reservations in a way that can be easily indexed using both bed_id and a date. You can turn this into a two-tier structure if necessary, but it's usually not.

When iterating:

- current_user.beds.each do |bed|
  - reservation = @reservations[[bed.id,date]]
  - if reservation
    # ...

This practice of selectively, but aggressively eager-loading records usually works quite well when you're dealing with complex inter-dependencies that can't be easily expressed with an includes(..) element in your loading chain.

Also, remember in Ruby there's only two things that are logically false: literal false and nil. Everything else evaluates as logically true, including 0, empty strings, arrays and hashes. As such comparisons != nil are almost always extraneous and confusing, especially if you do double-negation like unless (x != nil).

If you're looking to be able to resolve a number of arbitrary date+bed pairs against the database you could make some kind of booking key that was a combination of date and bed_id then it'd be a lot easier to scan for these. You could do a WHERE booking_token IN (...) and have it all indexed, performant, and concise. It takes some advance planning though to do it properly. YYYY-MM-DD-bed_id could work as a first pass.