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:

  1. Ticket

  2. User

  3. 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.