Search if vehicle is available between dates

Question

I've created a application where a user can reserve a vehicle (minibus) I've got the application working where a user can reserve a vehicle but the vehicle cannot be double booked. I've got a simple search working which allows the user to search for make or model. I'm looking to expand this search so a user can search for all vehicles which are available for a date the user wants to book. I'm not too sure how to approach this can someone please help!.

I've got a Vehicle table, User table and a Reservation table.

my schema look like (I've only included the tables concerned):

create_table "reservations", force: :cascade do |t|
  t.date "startDate"
  t.date "endDate"
  t.integer "noOfDays"
  t.integer "costs"
  t.integer "vehicle_id"
  t.integer "user_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["user_id"], name: "index_reservations_on_user_id"
  t.index ["vehicle_id"], name: "index_reservations_on_vehicle_id"
end
create_table "users", force: :cascade do |t|
  t.string "address"
  t.string "city"
  t.date "dateofbirth"
  t.string "email"
  t.string "fname"
  t.string "postcode"
  t.string "sname"
  t.string "user_type"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.string "password_digest"
  t.string "contact_no"
end
create_table "vehicles", force: :cascade do |t|
  t.string "description"
  t.integer "door"
  t.string "fuel"
  t.string "img"
  t.string "make"
  t.string "model"
  t.date "motEnd"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.date "motDue"
  t.date "motStart"
  t.integer "price"
  t.string "reg"
  t.integer "seats"
  t.string "transmission"
  t.date "year"
  t.decimal "engine"
end

I've already got the overlap booking working heres a like to the solution I used: Avoid double bookings for a minibus reservation system

I'm trying to expand my search so the user can search between two dates on the index page for all available vehicles during that time. I just don't know how I'd get this to work so the system shows available vehicles for that date.

My current simple search:

Vehicles controller:

def vehicle_search
    vehicles = Vehicle.fuzzy_search(params[:search_string])
    @vehicles = Kaminari.paginate_array(vehicles.order :make).page(params[:page]).per(3)
    if @vehicles.empty?
        flash.now[:alert] = "No records found - displaying all records ..."
      @vehicles = Vehicle.order(:make).page(params[:page]).per(3)
    end

    render :action => "index"

end

vehicle.rb

def self.fuzzy_search(search_string)
    search_string ||= ""  
    make = search_string
    model = search_string
    price = search_string
    fuel = search_string
    transmission = search_string
    self.where("make LIKE ? OR model LIKE ? OR price LIKE ? OR fuel LIKE ? OR transmission LIKE ?", make, model, price, fuel, transmission)
end

Search partials

<%= form_tag my_path, :method => 'post', :multipart => true do %>
  <!--vehicle details-->
  <%= text_field_tag :search_string, "", class: 'form-control custom2'  %>
  <%= submit_tag 'Search', class: "btn btn-default" %>
<% end %>

vehicles/index.html.erb

<a>Make, Model, Fuel Type, Transmission: </a>
<%= render(:partial=>'/vehicle_search',:locals=> { :my_path => "/vehicles/vehicle_search" })%>

Most probably you'd want to add which database you're using. — Sebastian Palma, Apr 09 '18 at 13:03
How do your tables look like? Please show their structure and some example data. — Stefan, Apr 09 '18 at 14:28

3limin4t0r · Answer 1 · 2018-04-09T14:19:13.280

I will be working with the following logic to determine a time overlap:

(StartA <= EndB) and (EndA >= StartB)

If the above is true the periods overlap. You can check this out in the Stack Overflow question: Determine Whether Two Date Ranges Overlap

So with that out of the way let's create two scopes on Reservation. One to get overlapping reservations that overlap with one point in time, and one to get overlapping reservations that overlap with a time window.

class Reservation < ApplicationRecord

  # code ...

  # assuming Reservation has start_time and end_time attributes
  # and you validate that start_time is always before end_time

  # time param can also be a date or datetime
  def self.overlap(time)
    # Reservation start_time is less than or equal to the given time
    # and reservation end_time is greater than or equal to the given 
    # time.
    where(arel_table[:start_time].lteq(time))
      .where(arel_table[:end_time].gteq(time))
  end

  def self.overlap_window(start_time, end_time)
    # Reservation start_time is less than or equal to the given
    # end_time and reservation end_time is greater than or equal 
    # to the given start_time.
    where(arel_table[:start_time].lteq(end_time))
      .where(arel_table[:end_time].gteq(start_time))
  end

  # code ...

end

Now you can fetch the vehicles quite easy by checking which vehicles are not occupied in for example a certain time window.

Vehicle.where.not(id: Reservation.select(:vehicle_id).overlap_window(Time.now, 4.hours.from_now))

Note: Start time must always be before the end time (for both the reservation and the time window) for this to work as explained (mainly in the comments) in the date overlap question linked at the start.

Search if vehicle is available between dates

1 Answers1