Searching for NOT existing associations

[ledermann]

I'm looking for a way to use Searchlogic to find records which DON'T HAVE records for a given association. For example: "Find all Contacts which have no addresses"

class Contact < ActiveRecord::Base
  has_many :addresses
end
class Address < ActiveRecord::Base
  belongs_to :contact
end

In SQL, this query does the job:

SELECT contacts.* FROM contacts LEFT JOIN addresses ON (addresses.contact_id = contacts.id) WHERE (addresses.ID IS NULL) 

So far as I understand, Searchlogic does not support this. Of course, I can creare a named scope for this:

class Contact
  named_scope :without_addresses, :joins => 'LEFT JOIN addresses ON addresses.contact_id = contacts.id', :conditions => 'addresses.id IS NULL'
end

But I would like to have a more dynamic way, means Searchlogic creating this scope on the fly. What do you think?

[ghost]

Searchlogic has a method called left_outer_joins that will create the join for you. But to the best way to solve this is to just add a named scope for each association. I would probably just create another module and do it in there.

[ledermann]

Yes, creating scopes the explicit way will work. But I'm looking for a dynamic way. One more example:

Right now, with Searchlogic it's possible to find all contacts with an address in Germany:

>> Contact.search(:addresses_country_equals => 'DE')
=> "SELECT `contacts`.* FROM `contacts`   
    INNER JOIN `addresses` ON addresses.contact_id = contacts.id  
    WHERE (addresses.country = 'DE') "

This creates an INNER JOIN, works fine.

Now, I want to find contacts which don't have an address in Germany. It would be great if Searchlogic could do this, perhaps with this statement:

>> Contact.search(:without_addresses_country_equals => 'DE')
=> "SELECT `contacts`.* FROM `contacts`   
    LEFT JOIN `addresses` ON (addresses.contact_id = contacts.id 
                              AND addresses.country = 'DE')  
    WHERE (addresses.id IS NULL) "

The idea is: If the hash key has a "without" in front of the association name, Searchlogic builds the query with the LEFT-JOIN/ID-IS-NULL-Pattern.

IMHO such a feature (implemented in Searchlogic) would be very powerful.

[ledermann]

One addition: The whole thing with the left join is required to include contacts which does not have any address, too. You may think my example can be reached with this simple query:

Contact.search(:addresses_country_is_not => 'DE')

But this gives a different result set, because it does not include the contacts which have no addresses at all.