Home Ask Login Register

Developers Planet

Your answer is one click away!

Benjamin Schneider February 2016

Querying rental availability for date range

I am currently working on a web application to manage device rentals. I have devices that belong to one device type and are associated to rentals with a start- and end date.

I now want to display how many devices are available per type on each day in a given range, like so:

| Device Type: | 01.01 | 02.01 | 03.01 | 04.01 | 05.01 |
--------------------------------------------------------
|Laptop        |   10  |   10  |   10  |    5  |    5  |
|Mobile Phone  |   30  |   25  |   25  |   10  |   10  |

At the moment I find out the available devices for any given date as follows:

  1. Find any rentals that overlap with the date
  2. Get devices associated with those rentals
  3. All devices not in 2. are available

The respective code looks like this:

  def Device.available_devices(start_date, end_date)
    other_rentals = Rental.where("outbound_delivery_date <= ? AND inbound_delivery_date >= ?", end_date, start_date)

    unavailable_devices = DeviceRental.where("rental_id IN (?)", other_rentals.map(&:id))

    if other_rentals.any? && unavailable_devices.any?
      @available_devices = Device.where("NOT devices.id IN (?)", unavailable_devices.map(&:device_id)).joins(:device_type).order("device_types.name")
    else
      @available_devices = Device.all
    end
  end

Now displaying this in a table means going through 1.-3. for each date and therefore causes an immense amount of DB queries which seems inefficient. Could anyone point me in the right direction of how I can get this data in a more efficient way?

Answers


SteveTurczyn February 2016

You can do this in a single query by using NOT IN

available_devices = Device.where('devices.id NOT IN (SELECT device_id FROM device_rentals WHERE rental_id IN (SELECT rentals.id from rentals WHERE outbound_delivery_date <= ? AND inbound_delivery_date >= ?))', end_date, start_date)

to get the count...

available_devices.count

Post Status

Asked in February 2016
Viewed 2,132 times
Voted 14
Answered 1 times

Search




Leave an answer


Quote of the day: live life