CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
bigmugcup
bigmugcup

Reputation: 1351

Rails Activerecord: How to exclude records with multiple associated records

I have the below query with the associations as shown.

The query works when there is only one booking record per bike, but fails when bike has multiple bookings and one or more meet the conditions and others do not. Since the other don't meet the conditions, they cause the associated bike to be returned as a result.

How do I write the query so that a bike is not returned as a query result when at least one of the bookings meets the where.not condition?

class Bike < ActiveRecord::Base
  has_many :bookings
end 

class Booking < ActiveRecord::Base
  belongs_to :bike
end 

startDate = Date.parse("2018-10-10")
endDate = Date.parse("2018-10-19")

@bikes = Bike.joins(:bookings).where.not("(date_start BETWEEN (?) AND (?)) OR (date_start BETWEEN (?) AND (?)) OR (((?) OR (?)) BETWEEN date_start AND date_end)", startDate, endDate, startDate, endDate, startDate, endDate)

Upvotes: 1

Views: 1669

Answers (3)

teckden
teckden

Reputation: 337

I think you can simplify the condition a bit. I have not considered edge cases but you can do something like this:

Bike.where.not(
    id: Booking.where("start_time < ? and end_time > ?", endDate, startDate
).group(:bike_id))

So basically bookings.start_time should be less then potential endDate while bookings.end_time is bigger than potential startDate. Hence you will get all bookings that are in the range of the potential new booking time range and adding not will select the opposite

I've tested my sample on a simple dataset and it excludes the bikes that are occupied. Please let me know if that works for you

Upvotes: 0

John Skiles Skinner
John Skiles Skinner

Reputation: 2028

You could reverse things, beginning the query with the bookings you want and finding the set of all their unique bikes:

@bikes = Booking.where.not(...).group(:bike_id).includes(:bikes).map{|b| b.bike}

Upvotes: 0

spickermann
spickermann

Reputation: 106802

You could run the query on Bike with a subquery on Booking:

Bike.where.not(
  id: Booking.select('DISTINCT bike_id').where(...)
)

Upvotes: 2

Related Questions