Open ledermann opened 15 years ago
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.
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.
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.
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"
In SQL, this query does the job:
So far as I understand, Searchlogic does not support this. Of course, I can creare a named scope for this:
But I would like to have a more dynamic way, means Searchlogic creating this scope on the fly. What do you think?