metasearch has_many where all/none has to match

Im trying to make a metasearch or alternatively a scope that gives me all objects that doesnt have any of its has_many-association equal to type == "Something".

Example:

class Order < ActiveRecord::Base
  has_many :billing_base
end

class InvoiceBase < ActiveRecord::Base
  belongs_to :order
end

class Invoice < InvoiceBase
end

class OrderAcknowledgement < InvoiceBase
end

Searching for orders that have an invoice is easily done by a custom scope:

joins(:invoice_base).where(:invoice_base => {:type => "Invoice"})

or metasearch:

:invoice_base_type_equals => "Inv开发者_JAVA百科oice"

Now how do I do the opposite, find orders that have NO invoice? (OrderAcknowledgements should always be allowed)

When trying to figure this out on my computer, i ended up writing a sql statement that involves a subquery. I wonder if maybe you can populate the raw SQL into the where method.

select * from orders where orders.id not in (SELECT invoice_bases.order_id from invoice_bases);

where("orders.id not in (SELECT invoice_bases.order_id from invoice_bases)")

I gave this a try on my site and it seemed to work. Note that I am using MySQL