Forum: Ruby on Rails ActiveRecord: preloading on relations with a select clause

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cf6548992752df0fe52d1ed6d5a100f4?d=identicon&s=25 unknown (Guest)
on 2015-12-02 09:50
(Received via mailing list)
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:
>  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.
This topic is locked and can not be replied to.