ruby on rails - Querying rental availability for date range -

i working on web application manage device rentals. have devices belong 1 device type , associated rentals start- , end date.

i want display how many devices available per type on each day in given range, 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 moment find out available devices given date follows:

1. find rentals overlap date
2. get devices associated rentals
3. all devices not in 2. available

the respective code looks this:

  def device.available_devices(start_date, end_date)     other_rentals = rental.where("outbound_delivery_date <= ? , 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 in table means going through 1.-3. each date , therefore causes immense amount of db queries seems inefficient. point me in right direction of how can data in more efficient way?

you can in single query using not in

available_devices = device.where('devices.id not in (select device_id device_rentals rental_id in (select rentals.id rentals outbound_delivery_date <= ? , inbound_delivery_date >= ?))', end_date, start_date) 

to count...

available_devices.count