Hello,
It turns out that our Apps Variant relation model + ActiveRecord Relations
produces some very funky behaviour which was preventing it from being used in
.includes preloads.
Background: Our App's variant model has a boolean column(composite) and they
reflect back on themselves through a subsidiary table
(composites). A Composite entry may reference a variant as a variant_id which
has many other composite variants which reference the former by parent_id. e.g.
Variants
id: 1
name: SnackBox
composite: true
id: 2
name: Snickers
composite: false
id: 3
name: Bounty
composite: false
Composites
id: 1 # references SnackBox
parent_id: null # is a parent
variant_id: 1
id: 2 #references snickers a composite of SnackBox
parent_id: 1
variant_id: 2
id: 3 #references bounty a composite of SnackBox
parent_id: 1
variant_id: 3
Here is the original code we used in the App to achieve loading this relation:
>
> class Variant < ActiveRecord::Base
> has_many :composites, foreign_key: :parent_id, dependent: :destroy
> has_many :composite_variants_singular, -> { select "variants.*,
> composites.quantity as composite_quantity" },
> through: :composites, source: :variant
> end
>
>
Here is what happens when you run the composite_variants on a single variant.
Variant Load (0.5ms) SELECT "variants".* FROM "variants" WHERE "variants"."id"
= $1 LIMIT 1 [["id", <ID>]]
>
>
And alternatively in an includes (notice that SELECT variants.* will fail in
the later):
Variant Load (0.7ms) SELECT variants.*, composites.quantity as
composite_quantity FROM "variants"
INNER JOIN "composites" ON "variants"."id" = "composites"."variant_id"
WHERE "composites"."parent_id" = $1 [["parent_id", <ID>]]
Here is the alternative code path I attempted:
>
> has_many :composite_variants_joined, -> {
> joins('''INNER JOIN "composites" ON "composites".variant_id =
> "variants".id''')
> .select "variants.*, composites.quantity as composite_quantity"
> },
> through: :composites, source: :variant
>
> The alternative query generated looks like this (which works correctly for
includes):
>
> Variant Load (89.5ms) SELECT "variants".* FROM "variants" WHERE "variants"
> ."account_id" = $1 [["account_id", <ID>]]
> Composite Load (53.6ms) SELECT "composites".* FROM "composites" WHERE
> "composites"."parent_id" IN (<ARRAY>)
> Variant Load (3.0ms) SELECT variants.*, composites.quantity as
> composite_quantity FROM "variants" INNER JOIN "composites" ON "composites"
> .variant_id = "variants".id WHERE "variants"."id" IN (<ARRAY>)
>
> But breaks tremendously on the single instance load.
Variant Load (0.4ms) SELECT "variants".* FROM "variants" WHERE "variants".
"id" = $1 LIMIT 1 [["id", <ID>]]
PG::DuplicateAlias: ERROR: table name "composites" specified more than once
: SELECT variants.*, composites.quantity as composite_quantity FROM
"variants" INNER JOIN "composites" ON "variants"."id" = "composites".
"variant_id" INNER JOIN "composites" ON "composites".variant_id = "variants"
.id WHERE "composites"."parent_id" = $1
The final solution I ended up with looked like this:
>
> class Variant < ActiveRecord::Base
> has_many :composites, foreign_key: :parent_id, dependent: :destroy
> has_many :parent_composites, class_name: "Composite"
> has_many :composite_variants, ->(variant) { _composite_variants(variant)
> },
> through: :composites, source: :variant
>
> def self.with_composite_quantity
> self.select("variants.*, composites.quantity as composite_quantity")
> end
> # This is a work-around to allow fetching composite variants for an
> single variant instance
> # as well pre-loading of of composite_variants via .includes - Since AR
> does not support a single
> # method for doing so we determine which method to use based on whether
> the lambda has a variant
> # instance passed in as an argument.
> def self._composite_variants(variant)
> if variant
> with_composite_quantity
> else
> self.joins(:parent_composites).with_composite_quantity
> end
> end
> end
>
> While I don't expect ActiveRecord Relations to handle every single use
case - the clearly incorrect SQL (duplicating the same INNER JOIN clause
twice) on the .includes() and the complexity of the final solution is quite
suspect, so I was hoping to have an outside opinion. Thanks for your time.
Alex
P.S. Sorry about the formatting the Google UI is wrapping all my attempts to
format code-blocks into single lines without the quotes.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/03851141-8e03-42c5-b46a-f504cbe18283%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
