Home > OS >  ActiveRecord .missing - Why doesnt this work?
ActiveRecord .missing - Why doesnt this work?

Time:07-01

Working on Rails 6.1

I've got this base class:

class Post < ApplicationRecord
end

And these two subclasses representing a face-to-face lesson and it's live transmission:

class Live < Post
  belongs_to :in_person_live,
             foreign_key: :live_of_in_person_live_id,
             class_name: 'InPersonLive',
             required: false,
             inverse_of: :online_live,
             touch: true
end

class InPersonLive < Post
  has_one :online_live,
          class_name: 'Live',
          foreign_key: 'live_of_in_person_live_id',
          inverse_of: :in_person_live,
          touch: true
end

I'd like to query for face-to-face lessons without a live transmission.

I came up with this, which seems to work:

InPersonLive.where.not(id: Live.where.not(live_of_in_person_live_id: nil).select(:live_of_in_person_live_id)).count 

But was wondering why this doesnt work:

InPersonLive.where.missing(:online_live).count 

I'd expect the .missing to return the same result but it always returns 0.

The generated SQL is different in both cases but again I don't understand why the result is different, seems to me like they should return the same set.

InPersonLive.where.not(id: Live.where.not(live_of_in_person_live_id: nil)).count generates:

SELECT COUNT(*) FROM "posts" WHERE "posts"."type" = $1 AND "posts"."id" NOT IN (SELECT "posts"."live_of_in_person_live_id" FROM "posts" WHERE "posts"."type" = $2 AND "posts"."live_of_in_person_live_id" IS NOT NULL)  [["type", "InPersonLive"], ["type", "Live"]]

while InPersonLive.where.missing(:online_live).count generates:

SELECT COUNT(*) FROM "posts" LEFT OUTER JOIN "posts" "online_lives_posts" ON "online_lives_posts"."live_of_in_person_live_id" = "posts"."id" AND "online_lives_posts"."type" = $1 WHERE "posts"."type" = $2 AND "posts"."id" IS NULL  [["type", "Live"], ["type", "InPersonLive"]]

CodePudding user response:

Hi @dwaynemac.

I wonder if you mapped or queried backward.

Maybe InPersonLive belongs to Live and Live has_one or has_many. Or maybe you should query Live.where.missing(:in_person_live).count

So, similarly Something.where.missing(:thing) should generate the same query.

Because, if you wanted to test prior to missing you would do something along the lines of:

Live.left_joins(:in_person_live).where(in_person_live: { id: nil } )

CodePudding user response:

The other answer already gave you the manual way of doing this, just wanted to add that this actually looks like it might be a bug. The AND "posts"."id" IS NULL should be AND "online_lives_posts"."id" IS NULL, ie. the null check should be for the left outer joined relation.

Obviously your associations are quite the mouthful, so not sure what's tripping it up, my first guess would be the STI.

  • Related