active record query over datetime array

Given I have an array of datetimes:

array = [Sat, 30 Jul 2011 00:00:00 CEST +02:00, Sat, 30 Jul 2011 00:15:00 CEST +02:00, Sat, 30 Jul 2011 00:30:00 CEST +02:00, Sat, 30 Jul 2011 00:45:00 CEST +02:00

I want my model class method to return the datetimes that dont match (aren't scheduled)

 Sat, 30 Jul 2011 00:00:00 CEST +02:00

#appointment.rb (with colum `date` as DateTime)

def self.booked(array)
  where("date开发者_开发百科 NOT IN (?)", array)

end

Thx for advise!

It's because your array is malformed:

array = [Sat, 30 Jul 2011 00:00:00 CEST +02:00, Sat, 30 Jul 2011 00:15:00 CEST +02:00, Sat, 30 Jul 2011 00:30:00 CEST +02:00, Sat, 30 Jul 2011 00:45:00 CEST +02:00

notice it's separating the days as individual elements. Or is this your doing? If not, you should probably convert them all to actual DateTime objects (using #parse probably) and then put them in the array.

Also, you might want to make that method into a scope:

scope :booked, lambda { |datetimes| where("date NOT IN (?)", datetimes) }

I'm guessing that date is actually a date in the database, not a datetime or timestamp. So, you need to convert your Ruby Datetimes to just Date instances:

def self.booked(array)
  where("date NOT IN (?)", array.map(&:to_date))
end

If you don't convert them to Dates by hand, AR won't know that it is supposed to convert them to dates for the database; then, depending on the underlying database, you could get empty results or possibly errors.

Your code look fine to me, but you need to provide an array, like this:

where(['date NOT IN(?)', array])

Here is an example with a Webinar class with a date column

>> array = Webinar.all[0..2].map(&:date)
=> [Wed, 04 May 2011 02:16:00 PDT -07:00, Tue, 05 Apr 2011 06:00:00 PDT -07:00, Thu, 30 Jun 2011 07:30:00 PDT -07:00]
>> count_without_array = (Webinar.count - array.size)
>> Webinar.where(['date NOT IN(?)', array]).count == count_without_array
=> true

Keep in mind that date must be exact match, if there is 1 second difference its not going to work.