ActiveRecord query condition where associated polymorphic record does not exist OR does/does not have condition
Recently, I had to make a query on a polymorphic association. Here’s the structure of the models involved:
Ticket
User
Reminder (belongs to Ticket as a remindable polymorphic association)
Basically, a user has many tickets and has many reminders. A ticket also has many reminders. The reminder and ticket models are connected via a polymorphic association as other items in the app could also have a reminder associated with them.
I wanted to be able to grab all the tickets a user had where no reminders for that ticket exists or if there are no reminders that exist that belonged to them. This is for a multi-user set up where the query would depend on which user was logged in. In this case, I didn’t want users who already set a reminder to be able to see the ticket, but at the same time I wanted other users to be able to see the ticket in case they wanted to act on it.
Rails 6.1 introduces the :missing modifier for ActiveRecord queries
As explained here, Rails 6.1 added a very useful ActiveRecord query to find records where it is missing a certain association. Unfortunately, this would also exclude records that I needed — mainly ticket records that had a reminder already, but didn’t belong to the current logged in user.
Use custom SQL join statements
In the end, I had to craft my own SQL statements — something I didn’t particularly enjoy doing after working with Rails’ great ORM for many years. Nonetheless, after a spending (read: wasting) some time trying to figure things out, I relented and pulled out my SQL reference.
To save others who might be facing the same situation, here’s the query:
Ticket.
joins("LEFT OUTER JOIN reminders ON reminders.remindable_type = 'Ticket' AND reminders.remindable_id = tickets.id").
where("reminders.id IS NULL OR reminders.user_id != ?", current_user.id)
Here’s a digram that explains what a “LEFT OUTER JOIN” is:
Source: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
What we want to do is exclude the results that meet the conditions we set here:
reminders.remindable_type = 'Ticket' AND reminders.remindable_id = tickets.id
Then, we use a custom where statement to define the conditions of what we want to include:
reminders.id IS NULL OR reminders.user_id != ?
And that’s it.
My actual query is a bit longer than this (and included other conditions, preloads, etc). The problem with using the :missing modifier was that I couldn’t also use the :or operator because the SQL statement generated was incompatible. You see, ActiveRecord/AREL’s :or operator is “incompatible” with left outer joins (and a whole bunch of other operators):
Relation passed to #or must be structurally compatible.
Rails’ ORM is great, but sometimes to get the query you want, it’s necessary to crack open the SQL cheatsheet.