Ruby on Rails Monday, November 30, 2015

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 rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
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.

No comments:

Post a Comment