SQL noob needs help writing a Rails query involving joins and maybe intersect

Question

I have 3 models: Books, Notifications, and NotificationTypes. Books have notifications as the Notification model has a book_id. Notifications have one notification_type as the Notification model has one notification_type_id

I want all Books created between date1 and date2

books_set1 = Book.find :all, :conditions => ["created_at <= ? AND show_time >= ?", max_date, min_date]

But I do not want books that have notifications of notification.notification_type_id = 1 and ideally i would like to say this by referring to notification_type.name so i would not want books that have notifications of notification.notification_type.name = 'type1'

If there has been a notification of type1 created for a book already, I do not want it returned in the set (because i am going to create notifications of that type with the returned set).

I am not sure if there is a way to do this in one query, I am thinking I need 2 queries with and INTERSECT - the first I already included in this post and the second i am not sure about. But in pseudo-code, i think this is what it needs to do:

notification_type_id = Notification.find_by_name('type1')
get all notifications where notification_id = notification_type_id
set2 = get the associated book set from the notification set (since each notification has one book)

then i do set1 - set2

UPDATE

Thanks to some help I have written two queries that get me the desired results. I would love for this to be in 1 query if anyone knows how to do it:

books_in_range = Book.find :all, :conditions => ["created_at <= ? AND created_at >= ?", max_date, min_date]
books_without_these_notifications = Book.find(:all, :joins => { :notifications => :notification_type }, :conditions => ["notification_types.name = ?","type1"] )
books_to_consider = books_in_range - books_without_these_reminders

Again, the idea is to get all books that do not have a notification of type1 created and fall within a specific date range.

Answer 1

SELECT books.* FROM books WHERE books.created_at <= ? AND books.show_time >= ? AND 
  notifications.name <> 'type1'
LEFT OUTER JOIN notifications ON notifications.book_id = books.id AND 
  notification.name = 'type1'

That would work, I would imagine.

Answer 2

The following should work:

Book.scoped(:conditions => ["created_at <= ? AND show_time >= ?", max_date,
  min_date]).find(:all, :joins => { :notifications => :notification_types },
  :conditions => ["notification_type.name <> ?", "type1"])

Extract some of the arguments into named_scopes or local variables for extra clarity.

Updated to reflect the negation.

Answer 3

You could do it with NOT EXISTS but I'm not sure how efficient it would be:

Book.find(
  :all, 
  :conditions => 
    ['created_at <= ? AND show_time >= ? AND 
      NOT EXISTS 
        (SELECT * FROM notifications 
         INNER JOIN notification_types ON notifications.notification_type_id = notification_types.id 
         WHERE notification_types.name = ? AND notifications.book_id = books.id)',
     max_date, min_date, 'type1'
    ])

You might be able to come at it from a different direction and keep track of those which have had a notification of type1 sent out by adding a boolean to books or some other object joined directly to books. Then your query could be:

Book.find(:all, 
  :conditions => 
    ['created_at <= ? AND show_time >= ? AND 
      type1_notification_sent = ?', 
     max_date, min_date, false
    ])