Filtering multiple conditions through associated objects

[sridharraman]

My User model has the following association: user has_many trips, through registrations.

If the search query is "Users who have gone on trip named 'ABC'", the SQL generated is fine:

SELECT DISTINCT users.* FROM users LEFT OUTER JOIN registrations ON registrations.user_id = users.id LEFT OUTER JOIN trips ON trips.id = registrations.batch_id WHERE (trips.name LIKE '%ABC%')

If the search query is "Users who have gone on trips 'ABC' and 'DEF'", how does one do it? I tried two different approaches:

1. Two separate conditions with name = 'ABC' and name = 'DEF'. This was done with "matches all". The SQL gave 0 results (obviously) as this was generated:

SELECT DISTINCT users.* FROM users LEFT OUTER JOIN registrations ON registrations.user_id = users.id LEFT OUTER JOIN trips ON trips.id = registrations.batch_id WHERE ((trips.name LIKE '%ABC%' AND trips.name LIKE '%DEF%'))

2. I tried "matches any" instead. That gave this:

SELECT DISTINCT users.* FROM users LEFT OUTER JOIN registrations ON registrations.user_id = users.id LEFT OUTER JOIN trips ON trips.id = registrations.batch_id WHERE ((trips.name LIKE '%ABC%' OR trips.name LIKE '%DEF%'))

The 2nd query results were those users who had either gone on ABC or DEF.

To add to this, we wanted this query: "Users who have gone on ABC but not on DEF". I tried this with two conditions (matches all): trip.name = ABC and trips.name not like DEF. This was the SQL generated:

SELECT DISTINCT users.* FROM users LEFT OUTER JOIN registrations ON registrations.user_id = users.id LEFT OUTER JOIN trips ON trips.id = registrations.batch_id WHERE ((trips.name LIKE '%ABC%' AND trips.name NOT LIKE '%DEF%'))

The above query isn't right. How does one get the right query using Ransack?