ActiveRecord: preloading on relations with a select clause

[alexande...@gmail.com]

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.