ransack_demo
ransack_demo copied to clipboard
Filtering multiple conditions through associated objects
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:
- 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%'))
- 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?