CODEWITHSUNDEEP
ruby-on-railsactiverecordquerying
David Geismar
David Geismar

Reputation: 3422

Active record get all instance that do not match condition

I have the following AR query which returns an array of rooms (the rooms that are not available in the time period given) :

rooms = Room.joins(:bookings).where("(bookings.start_date >= ? AND bookings.start_date <= ?) OR (bookings.end_date >= ? AND bookings.end_date <= ?) OR (bookings.start_date <= ? AND bookings.end_date >= ?)", from, to, from, to, from, to)

I want to modify that query so it returns all the other rooms; that is the ones that are available in the requested time period. The right result is givent by the following :

all_rooms = Room.all
available_rooms = all_rooms - rooms

But I directly want to get the available rooms in a single query. I already added a .not but it doesnt give me the right result :

rooms = Room.joins(:bookings).where.not("(bookings.start_date >= ? AND bookings.start_date <= ?) OR (bookings.end_date >= ? AND bookings.end_date <= ?) OR (bookings.start_date <= ? AND bookings.end_date >= ?)", from, to, from, to, from, to)

How should I modify the query ?

Upvotes: 0

Views: 425

Answers (1)

Hieu Pham
Hieu Pham

Reputation: 6707

Let's say:

A = (bookings.start_date >= ? AND bookings.start_date <= ?)
B = (bookings.end_date >= ? AND bookings.end_date <= ?) 
C = (bookings.start_date <= ? AND bookings.end_date >= ?)
first_logic  = A or B or C
second_logic = not(first_logic) = not(A or B or C) = not(A) and not(B) and not(C)

available_rooms is the condition of second_logic, so it will be like:

available_rooms = Room.joins(:bookings)
                      .where.not("bookings.start_date >= ? AND bookings.start_date <= ?", from, to)
                      .where.not("bookings.end_date >= ? AND bookings.end_date <= ?", from, to)
                      .where.not("bookings.start_date <= ? AND bookings.end_date >= ?", from, to)

Upvotes: 1

Related Questions