I will remember to post the sql output next time. I tried using the references solution earlier but that was not an acceptable solution because it created a really large query and crippled my performance.
After testing it. It seems you're first proposal will do the job for me. I was hoping for a solution that didnt involve multiple join associations because i will end up with quite a few of them since i have like five different comment types. Anyway thanks for your help Matt and Fred. Flemming Den torsdag den 14. januar 2016 kl. 15.44.24 UTC+1 skrev Matt Jones: > > > > On Wednesday, 13 January 2016 16:59:34 UTC-5, Flemming Thesbjerg wrote: >> >> >> I am trying to optimize a slow query with an includes statement. >> >> >> But a join table with a polymorphic association seem to be preventing it. >> >> >> The following gist tries to illustrate the issue: >> https://gist.github.com/flemse/fdd51ff5ad29a1f57134 >> >> >> When running the code from the gist it will fail to load the join table >> and therefore fail. >> >> >> Any help would be greatly appreciated. >> > > It would be helpful to see the exact SQL generated when the tests in that > Gist run. People can run the example, but it's an extra step. > > The query fails because the `post_artifacts` table isn't joined. This is > an expected behavior of `includes`; it chooses between a preload (which > requires one additional query but fewer joins) and an eager load (which > widens the query with a join). To do this, it relies on checking to see if > the included tables are referenced in the SQL. Putting conditions on the > join table (post_artifacts, here) in a through association (a_comments, > here) without hinting will cause this behavior. > > There are at least two ways to work around this problem: > > * first alternative: explicitly build an association of PostArtifacts that > have the condition applied. Change the Post model to: > > class Post < ActiveRecord::Base > has_many :post_artifacts > has_many :comments, through: :post_artifacts, source: :artifact, > source_type: 'Comment' > has_many :a_post_artifacts, -> { a }, class_name: 'PostArtifact' > has_many :a_comments, through: :a_post_artifacts, source: :artifact, > source_type: 'Comment' > end > > This moves the condition to a place where ActiveRecord understands the > `post_artifacts` table will be referenced when preloading `a_comments`. The > resulting SQL looks like: > > SELECT "posts".* FROM "posts" > SELECT "post_artifacts".* FROM "post_artifacts" WHERE > "post_artifacts"."rule" = ? AND "post_artifacts"."artifact_type" = ? AND > "post_artifacts"."post_id" = 1 [["rule", 0], ["artifact_type", "Comment"]] > SELECT "comments".* FROM "comments" WHERE "comments"."id" IN (1, 2, 3, 4, > 5, 6, 7, 8, 9, 10) > > * second alternative: explicitly specify `references` at the callsite. > Leave the associations as-is from the Gist and change the second assert in > the test to: > > assert_equal 10, > Post.includes(:a_comments).references(:post_artifacts).flat_map(&:a_comments).count > > This uses `references` to inform ActiveRecord that loading the requested > Posts also requires post_artifacts. > > The generated SQL looks different than the previous case, as `references` > forces eager-load instead of preload: > > SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "comments"."id" AS > t1_r0, "comments"."content" AS t1_r1 FROM "posts" LEFT OUTER JOIN > "post_artifacts" ON "post_artifacts"."post_id" = "posts"."id" AND > "post_artifacts"."artifact_type" = ? LEFT OUTER JOIN "comments" ON > "comments"."id" = "post_artifacts"."artifact_id" AND > "post_artifacts"."rule" = ? [["artifact_type", "Comment"], ["rule", 0]] > > ---- > > One thing that *doesn't* work yet: specifying `references` in the scope > passed to `has_many`. > > --Matt Jones > -- 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/e0565380-fefe-4f7e-bf37-caf95ad2e988%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
