How to check if a time range is overlapping in Rails ? (Airbnb like application)

Question

I'm trying to develop an Airbnb like Rails application but I'm blocked for the overlapping check system; when we check if the place is already booked or not. The difficulty here is I need to check if the date is overlapping but also the time. Because in my project you can rent a place for a few hours only if you want.

Schema:

  create_table "bookings", force: :cascade do |t|
    t.integer "user_id"
    t.integer "place_id"
    t.datetime "start_time"
    t.datetime "end_time"
    t.integer "price"
    t.integer "total"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["place_id"], name: "index_bookings_on_place_id"
    t.index ["user_id"], name: "index_bookings_on_user_id"
  end

  create_table "places", force: :cascade do |t|
    t.integer "user_id"
    t.string "name"
    t.integer "price"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["user_id"], name: "index_places_on_user_id"
  end

Do you have an idea to implement this overlapping check in a clean way?

Maybe, you can join places with bookings and check what places are free for certain period. Something like this: `Place.joins('LEFT JOIN bookings ON bookings.place_id = places.id').where('bookings.id IS NULL OR (bookings.start_time *** ? AND bookings.end_time *** ?)', some_start_time, some_end_time)`. *** have to be replaced with appropriate operators like `>=` or `>` or `<=` etc. The `bookings.id IS NULL` of where responds for getting places without any bookings. — user3309314, Dec 15 '17 at 18:04

max · Answer 1 · 2017-12-15T20:05:15.213

What you want is a BETWEEN query:

booked = Place.joins(:bookings)
  .where('? BETWEEN bookings.start_time AND bookings.end_time OR 
          ? BETWEEN bookings.start_time AND bookings.end_time', 
          starts_at, ends_at)

You can create a scope method to avoid repeating this:

class Booking
  def self.between(starts_at, ends_at)
    where(
     '? BETWEEN bookings.start_time AND bookings.end_time OR ? BETWEEN bookings.start_time AND bookings.end_time', 
     starts_at, ends_at
    )
  end
end

Since .joins creates a left inner join only rows with a match in the join table will be returned. To get the inverse the simplest way is to do it in two queries:

available = Place.where.not(id: booked)

You can also use a left outer join:

available = Place.left_outer_joins(:bookings)
     .where(bookings: Booking.between(starts_at, ends_at))
     .where(bookings: { id: nil })

This will remove any rows from places with a match in bookings. .left_outer_joins was added in Rails 5.

Thanks for your answer! Can you please explain how to check the value of `available`? I've tried: `if available puts "Available" else puts "Not available" end` But it's not working, always available :/ — Lionel Paulus, Dec 16 '17 at 13:38
Ok I've found a solution: `available = Place.where.not(id: booked).exists?`. With this solution it works perfectly, thanks! But I've tried to replace `booked = Place.joins(:bookings).where(...` by `booked = Place.joins(:bookings), Booking.between(start_time, end_time)` to don't have any SQL query in my controller and it doesn't work; always not available. Do you know why? — Lionel Paulus, Dec 16 '17 at 14:17

How to check if a time range is overlapping in Rails ? (Airbnb like application)

1 Answers1