ActiveRecord: preloading on relations with a select clause

Rails
1

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”, ]]

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”, ]]
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”, ]]
Composite Load (53.6ms) SELECT “composites”.* FROM “composites” WHERE
“composites”.“parent_id” IN ()
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 ()

But breaks tremendously on the single instance load.
Variant Load (0.4ms) SELECT “variants”.* FROM “variants” WHERE
“variants”.
“id” = $1 LIMIT 1 [[“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:

end
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.