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 join
ed relation.
Obviously your associations are quite the mouthful, so not sure what's tripping it up, my first guess would be the STI.