How to create a custom where.missing query for complex has_many through association

Question

I have a complex has_many through assocation which looks like the below:

class Artist < ApplicationRecord
  has_many :artist_masters, ->(artist) {
    unscope(:where).where(artist_id: artist.id)
                   .or(where(composer_id: artist.id))
                   .or(where(performer_id: artist.id))
                   .or(where(conductor_id: artist.id))
  }
  has_many :masters, through: :artist_masters
end

class Master < ApplicationRecord
  has_many :artist_masters, dependent: :destroy
  has_many :artists, through: :artist_masters
  has_many :composers, class_name: 'Artist', through: :artist_masters
  has_many :performers, class_name: 'Artist', through: :artist_masters
  has_many :conductors, class_name: 'Artist', through: :artist_masters
end

class ArtistMaster < ApplicationRecord
  belongs_to :master
  belongs_to :artist, optional: true
  belongs_to :composer, class_name: 'Artist', optional: true
  belongs_to :performer, class_name: 'Artist', optional: true
  belongs_to :conductor, class_name: 'Artist', optional: true
end

Thanks to this post for the help with that.

However this stops Artist.where.missing(:masters) or Artist.where.missing(:artist_masters) from working.

I need to recreate this so that it returns all Artists who's id is not present in any artist_id, performer_id, composer_id or conductor_id.

If i run Artist.where.missing(:artist_masters).first I get an error:

ArgumentError: The association scope 'artist_masters' is instance dependent (the scope block takes an argument). Preloading instance dependent scopes is not supported.

If i run Artist.where.missing(:masters).first I get an error:

NoMethodError: undefined method 'id' for nil:NilClass

    unscope(:where).where(artist_id: artist.id)
                                           ^^^

I don't get an error, just this `#` when I run the query, which isn't very helpful. — Pseudo-Hippy, Jun 17 '22 at 14:14
Try to add `.first` to the query and it should return the error. — Vishal Jain, Jun 17 '22 at 14:31
@VishalJain `[2] pry(main)> Artist.where.missing(:masters).first NoMethodError: undefined method 'id' for nil:NilClass unscope(:where).where(artist_id: artist.id)` on artist.id. — Pseudo-Hippy, Jun 17 '22 at 14:50

score 0 · Answer 1 · answered Jun 17 '22 at 18:35

0

Try this query:

Artist.where.not(id: ArtistMaster.select(:performer_id)).
       where.not(id: ArtistMaster.select(:composer_id)).
       where.not(id: ArtistMaster.select(:conductor_id))

Does it give you what you need? Is it performant?

answered Jun 17 '22 at 18:35

Les Nightingill

5,662
1
29
32

This just gives me an empty array – Pseudo-Hippy Jun 20 '22 at 08:48
have you checked your data (use Ruby in the Rails console) to be sure there are actually results that match the criteria? What is the SQL query produced? Obv I don't have the exact models as your app, but it's working for me. – Les Nightingill Jun 20 '22 at 16:02
I added `.where.not(id: ArtistMaster.select(:artist_id)).` to the chain as it seemed it was missing, with SQL produced being: `SELECT "artists".* FROM "artists" WHERE "artists"."id" NOT IN (SELECT "artist_masters"."artist_id" FROM "artist_masters") AND "artists"."id" NOT IN (SELECT "artist_masters"."performer_id" FROM "artist_masters") AND "artists"."id" NOT IN (SELECT "artist_masters"."composer_id" FROM "artist_masters") AND "artists"."id" NOT IN (SELECT "artist_masters"."conductor_id" FROM "artist_masters")`. Should be many results, but only empty array. – Pseudo-Hippy Jun 20 '22 at 16:37
Then I'm not understanding your models, or your question. In the rails console do `artist_ids=Artist.all.map(&:id).sort` and then `master_ids=ArtistMaster.all.map{|a| [a.artist_id,a.performer_id,a.composer.id,a.conductor_id]}.flatten.uniq.sort` and finally `result=artist_ids-master_ids`. Does the `result` array have any entries? – Les Nightingill Jun 20 '22 at 17:18

How to create a custom where.missing query for complex has_many through association

1 Answers1