rails: ActiveRecord::Relation#to_sql does not match SQL executed when using includes
1.9.3p194 :009 > Todo.includes(:user).where("users.id" => 1)
SQL (0.5ms) SELECT "todos"."id" AS t0_r0, "todos"."name" AS t0_r1, "todos"."user_id" AS t0_r2, "todos"."created_at" AS t0_r3, "todos"."updated_at" AS t0_r4, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."created_at" AS t1_r2, "users"."updated_at" AS t1_r3 FROM "todos" LEFT OUTER JOIN "users" ON "users"."id" = "todos"."user_id" WHERE "users"."id" = 1
=> [#<Todo id: 2, name: "Wash the car", user_id: 1, created_at: "2012-05-02 22:15:55", updated_at: "2012-05-02 22:15:55">, #<Todo id: 3, name: "Get a spare key made", user_id: 1, created_at: "2012-05-02 22:16:05", updated_at: "2012-05-02 22:16:05">]
1.9.3p194 :010 > Todo.includes(:user).where("users.id" => 1).to_sql
=> "SELECT \"todos\".* FROM \"todos\" WHERE \"users\".\"id\" = 1"
In looking at Issue #5990, @isaacsanders and I stumbled upon this issue.
We were attempting to make #pluck
generate the same query that is used in the standard finder, but discovered that it doesn’t use that query. We think this is the issue at the heart of #5990.
Thoughts?
About this issue
- Original URL
- State: closed
- Created 12 years ago
- Comments: 32 (17 by maintainers)
This is because eager loading is applied late at query execution and generates another
Relation
internally, referencing the join tables etc - whichto_sql
doesn’t account for.One option could be to alter
to_sql
to refer to a ‘final’ version of the relation to be used come query time, taking into account the logic fromexec_queries
(i.e. to useconstruct_relation_for_association_find
in that case)Flow diverges here: https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/relation.rb#L171
Query with eager loaded association(s) generates internal Relation via here: https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/relation/finder_methods.rb#L208-215
Relation#to_sql is doesn’t currently account for this, as seen here: https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/relation.rb#L456-458
EDIT: Relation arel used in
to_sql
is built here, and doesn’t consider eager loaded associations: https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/relation/query_methods.rb#L263-288