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?

Answer 1

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.