Rails 5: Query which excludes results if they're associated with a specific record

Question

I've got two models: Playlist and Item. I want to build a query which returns all playlists except those which have an specific item.

These are the models:

class Playlist < ApplicationRecord
  has_many :items
end

class Item < ApplicationRecord
  belongs_to :playlist
end

This is a query which doesn't work since there could be playlists which have the specified item, but also several more, and if they have other items, they are included (which is what I don't want):

Playlist.left_outer_joins(:items).where.not(items: { uid: id })

For the record, my DBMS is PostgreSQL 9.6

score 2 · Answer 1 · answered Jan 15 '19 at 17:46

Probably this can be written in a better way but it should work:

join_sql = Arel.sql(
  "LEFT OUTER JOIN items ON " \
  "(items.playlist_id = playlists.uid " \
  "AND items.some_id = '#{item.some_id}')"
)

Playlist.where(owner: owner)
        .joins(join_sql)
        .where(items: { playlist_id: nil })

The concept is similar to this one: https://stackoverflow.com/a/2686266

lurker · Accepted Answer · 2019-01-16T15:26:26.640

I don't have a setup to quickly test this, but I think you can use a Rails ActiveRecord subquery:

Playlist.where.not(id: Item.select(:pl_id).where(id: id_to_exclude))

Here, pl_id is the name of the attribute in Item corresponding to the playlist Id. I'm also assuming that id is the primary key in each of your tables. This might be a compact way for a result, but may not be the most efficient from a query perspective.

Rails 5: Query which excludes results if they're associated with a specific record

2 Answers2