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

user_907840 0
Viewed 57

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

postgresql activerecord ruby-on-rails sql
2 Answers

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

user_11742230

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.

user_9805500
Related