Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

includes eager loading is realized with LEFT OUTER JOIN strategy when querying with value contains 'dot' #7177

Closed
zhougn opened this Issue · 2 comments

3 participants

@zhougn

Given the keyword is 'abc' without a 'dot'

Post.where(:name => "abc").includes(:author)

There are two sqls used as normal

Post Load (0.8ms)  SELECT `posts`.* FROM `posts` WHERE `posts`.`name` = 'abc'
Author Load (0.4ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` IN (1)

Given the keyword is 'abc.' with a 'dot'

Post.where(:name => "abc.").includes(:author)

The sql is using LEFT OUTER JOIN strategy, that's confusing.

SELECT `posts`.`id` AS t0_r0, `posts`.`name` AS t0_r1, `posts`.`author_id` AS t0_r2, `posts`.`created_at` AS t0_r3, `posts`.`updated_at` AS t0_r4, `authors`.`id` AS t1_r0, `authors`.`created_at` AS t1_r1, `authors`.`updated_at` AS t1_r2 
FROM `posts` LEFT OUTER JOIN `authors` ON `authors`.`id` = `posts`.`author_id` 
WHERE `posts`.`name` = 'abc.'

My question

I know eager loading with includes is realized with LEFT OUTER JOIN strategy when there are conditions on the association, like

Post.includes(:author).where(:authors => {:name => 'zhougn' })

But in my test cases, there is no such a condition. Basically both Multi-SQL strategy and LEFT OUTER JOIN strategy can give me correct result, but when Posts and Authors are in different databases, LEFT OUTER JOIN strategy will fail.

@senny
Owner

This is due to the fact, that the method ActiveRecord::Relation#tables_in_string (code) recognizes abc. as a table name. On the master branch, this also results in a deprecation warning:

DEPRECATION WARNING: It looks like you are eager loading table(s) (one of: aircraft, peter) that are referenced in a string SQL snippet. For example: 

    Post.includes(:comments).where("comments.title = 'foo'")

Currently, Active Record recognises the table in the string, and knows to JOIN the comments table to the query, rather than loading comments in a separate query. However, doing this without writing a full-blown SQL parser is inherently flawed. Since we don't want to write an SQL parser, we are removing this functionality. From now on, you must explicitly tell Active Record when you are referencing a table from a string:

    Post.includes(:comments).where("comments.title = 'foo'").references(:comments)

@jonleighton any Ideas to solve this? Adjusting the Regexp to only detect real table names could be difficult.

@jonleighton
Collaborator

@senny the solution is that we are deprecating tables_in_string. there is no way to make the regexp work flawlessly. hence from Rails 4.1 users must explicitly use references as detailed in the above message. so we can close this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.