0

I am running into some difficulty re-writing a SQL query and translating it to an ActiveRecord query.

Say I have a table resources, which provides a list of resources identified by id (and containing other attributes).

Say I also have a table reservation_slots, which lists reservations on these resources as one hour slots, with attributes: id, start_datetime, reservation_id, and resource_id (which references resources.id). A multi-hour reservation on a resource will have multiple rows in this table.

I want to retrieve a list of all "unavailable" time slots: start_datetimes where all resources (in the resources table) are already reserved. I can retrieve such a list with the following SQL:

select start_datetime 
from reservation_slots
where not exists (
  select r.id 
  from resources r 
  where r.id not in (
    select resource_id
    from reservation_slots rs
    where rs.start_datetime = reservation_slots.start_datetime
  )
)

Is there a simpler or more efficient way to express this query? How would I write this as an ActiveRecord query without a lot of raw SQL in the where clause?

Jacob Brown
  • 7,221
  • 4
  • 30
  • 50
  • Do look for [duplicates](http://stackoverflow.com/questions/5483407/subqueries-in-activerecord) before asking – hd1 Jun 16 '13 at 22:34
  • Thanks for the link, @hd1, but it doesn't answer the first part of my question (re-writing the SQL), and I am familiar with the techniques mentioned for nesting AR queries. I guess [CodeReview](http://codereview.stackexchange.com/) might be a better place for this question. – Jacob Brown Jun 16 '13 at 22:47

0 Answers0