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?

cc @josevalim @tenderlove @steveklabnik

About this issue

  • Original URL
  • State: closed
  • Created 12 years ago
  • Comments: 32 (17 by maintainers)

Most upvoted comments

This is because eager loading is applied late at query execution and generates another Relation internally, referencing the join tables etc - which to_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 from exec_queries (i.e. to use construct_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