Jeremy Blalock Jeremy Blalock - 4 months ago 7
Ruby Question

ActiveRecord `.references` joining more tables than necessary? (and causing an error)

Model Layout



Article (has_many :attachments, :comments, :tags)
- id: string

Comment (has_many :attachments)
- id: integer
- article_id: string

Attachment
- id: integer
- reference_type: string
- reference_id: string

Tag
- id: integer
- article_id: string


Erroneous Query



In the
rails console
I run:

Article.includes(:tags, :comments => :attachments).references(:tags)


And the resulting error is:

Article.includes(:tags, :comments => :attachments).references(:tags)
SQL (0.7ms) SELECT "articles"."id" AS t0_r0, "articles"."created_at" AS t0_r1, "articles"."updated_at" AS t0_r2, "tags"."id" AS t1_r0, "tags"."article_id" AS t1_r1, "tags"."created_at" AS t1_r2, "tags"."updated_at" AS t1_r3, "comments"."id" AS t2_r0, "comments"."article_id" AS t2_r1, "comments"."created_at" AS t2_r2, "comments"."updated_at" AS t2_r3, "attachments"."id" AS t3_r0, "attachments"."reference_type" AS t3_r1, "attachments"."reference_id" AS t3_r2, "attachments"."created_at" AS t3_r3, "attachments"."updated_at" AS t3_r4 FROM "articles" LEFT OUTER JOIN "tags" ON "tags"."article_id" = "articles"."id" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" LEFT OUTER JOIN "attachments" ON "attachments"."reference_id" = "comments"."id" AND "attachments"."reference_type" = $1 [["reference_type", "Comment"]]
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: character varying = integer
LINE 1: ...OIN "attachments" ON "attachments"."reference_id" = "comment_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "articles"."id" AS t0_r0, "articles"."created_at" AS t0_r1, "articles"."updated_at" AS t0_r2, "tags"."id" AS t1_r0, "tags"."article_id" AS t1_r1, "tags"."created_at" AS t1_r2, "tags"."updated_at" AS t1_r3, "comments"."id" AS t2_r0, "comments"."article_id" AS t2_r1, "comments"."created_at" AS t2_r2, "comments"."updated_at" AS t2_r3, "attachments"."id" AS t3_r0, "attachments"."reference_type" AS t3_r1, "attachments"."reference_id" AS t3_r2, "attachments"."created_at" AS t3_r3, "attachments"."updated_at" AS t3_r4 FROM "articles" LEFT OUTER JOIN "tags" ON "tags"."article_id" = "articles"."id" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" LEFT OUTER JOIN "attachments" ON "attachments"."reference_id" = "comments"."id" AND "attachments"."reference_type" = $1
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `block in exec_no_cache'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `exec_no_cache'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:587:in `execute_and_clear'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/relation/finder_methods.rb:389:in `find_with_associations'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/relation.rb:699:in `exec_queries'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/relation.rb:580:in `load'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/relation.rb:260:in `records'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activerecord-5.0.0/lib/active_record/relation.rb:683:in `inspect'
... 1 levels...
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/railties-5.0.0/lib/rails/commands/console_helper.rb:9:in `start'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:78:in `console'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/railties-5.0.0/lib/rails/commands.rb:18:in `<top (required)>'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:293:in `require'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:293:in `block in require'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:259:in `load_dependency'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:293:in `require'
from /Users/jeremy/Documents/Synack/Code/experiments/joins-test/bin/rails:9:in `<top (required)>'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:287:in `load'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:287:in `block in load'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:259:in `load_dependency'
from /Users/jeremy/.rvm/gems/ruby-2.2.2@rails5/gems/activesupport-5.0.0/lib/active_support/dependencies.rb:287:in `load'
from /Users/jeremy/.rvm/rubies/ruby-2.2.2/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
from /Users/jeremy/.rvm/rubies/ruby-2.2.2/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'


Successful Query



Without a
.references()
clause, the query works properly, and fetches all models separately, creating four separate requests.

In the
rails console
I run:

Article.includes(:tags, :comments => :attachments)


And I see:

Article.includes(:tags, :comments => :attachments)
Article Load (5.1ms) SELECT "articles".* FROM "articles"
Tag Load (0.3ms) SELECT "tags".* FROM "tags" WHERE "tags"."article_id" = 'seedacorn-123'
Comment Load (0.2ms) SELECT "comments".* FROM "comments" WHERE "comments"."article_id" = 'seedacorn-123'
Attachment Load (0.3ms) SELECT "attachments".* FROM "attachments" WHERE "attachments"."reference_type" = $1 AND "attachments"."reference_id" IN ('1', '2') [["reference_type", "Comment"]]
=> #<ActiveRecord::Relation [#<article id: "seedacorn-123", created_at: "2016-07-15 22:41:27", updated_at: "2016-07-15 22:41:27">]>


The Question



So I guess, what I'm really wondering, is why ActiveRecord adding all of the included models to the
JOIN
rather than just those that I added to the
.references()
? Is this intended as a performance benefit? Or is this needed for some other reason?

I did not add
:comments
or
:comments => :attachments
to the
.references()
, but they were still added to the
JOIN
. This is causing a typecasting error, but I'm wondering why this is even happening in the first place?

Ask



If you know how to resolve this with some kind of creative solution, I'm trying desperately to figure it out. The core problems is that my polymorphic
Attachment
model can reference other models with either String or Integer primary keys, and so its
reference_type
is a string. Then this is not getting properly typecast when querying other models, in the case where
JOIN
's are introduced.

Answer

Found an answer to this question after posting an issue on github.com/rail/rails.

As it turns out, this is known (and unfortunately expected) behavior in Rails. If you require that an association be loaded with a separate query and not as part of a JOIN, you can use preload instead of includes, which will always load the association as in a separate query.

Likewise, if you want to force an association to load as part of a JOIN, and you're not using includes, you can replace references with eager_load.

So

Article.includes(:tags, :comments => :attachments).references(:tags)

Would become

Article.preload(:tags, :comments => :attachments).eager_load(:tags)
Comments