Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Side effects of the sub-SQL and multiple JOINS #13030

Closed
akicho8 opened this Issue Nov 25, 2013 · 2 comments

Comments

Projects
None yet
3 participants

akicho8 commented Nov 25, 2013

require "active_record"
ActiveRecord::VERSION::STRING   # => "4.0.1"
ActiveRecord::Base.logger = ActiveSupport::Logger.new(STDOUT)
ActiveSupport::LogSubscriber.colorize_logging = false
ActiveRecord::Migration.verbose = false
ActiveRecord::Base.establish_connection(:adapter => "sqlite3", :database => ":memory:")
ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :articles do |t|
    t.belongs_to :user
  end
end

class User < ActiveRecord::Base
  has_many :articles
end

class Article < ActiveRecord::Base
  belongs_to :user
end

s = User.joins(:articles).order("articles.id")

# (1) error
sub = User.joins(:articles)
s.joins("INNER JOIN (#{sub.to_sql})").to_a rescue $! # => #<ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: articles.id: SELECT "users".* FROM "users" INNER JOIN "articles" "articles_users" ON "articles_users"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id")  ORDER BY articles.id>

# (2) ok
sub = User.joins('INNER JOIN articles articles2 ON articles2.user_id = users.id')
s.joins("INNER JOIN (#{sub.to_sql})").to_a rescue $! # => []

# >>    (0.3ms)  CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL) 
# >>    (0.1ms)  CREATE TABLE "articles" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer) 
# >>   User Load (0.2ms)  SELECT "users".* FROM "users" INNER JOIN "articles" "articles_users" ON "articles_users"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id") ORDER BY articles.id
# >> SQLite3::SQLException: no such column: articles.id: SELECT "users".* FROM "users" INNER JOIN "articles" "articles_users" ON "articles_users"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id")  ORDER BY articles.id
# >>   User Load (0.1ms)  SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN articles articles2 ON articles2.user_id = users.id) ORDER BY articles.id

I was thinking that it looks like this.

SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id")  ORDER BY articles.id

It was different than expected.

SELECT "users".* FROM "users" INNER JOIN "articles" "articles_users" ON "articles_users"."user_id" = "users"."id" INNER JOIN (SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id")  ORDER BY articles.id

Encountered an error in the part of the ORDER BY articles.id for articles has been renamed to articles_users

This specification?

Anyway, when written as (2) with the trial and error, I was able to avoid the error. (I want to know if there is a way to write more neatly)

@akicho8 akicho8 added the stale label Apr 23, 2014

Owner

rafaelfranca commented May 1, 2014

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

@rails-bot rails-bot closed this May 27, 2014

This issue has been automatically closed because of inactivity.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment