ActiveRecord的查询与alias'd表名 [英] ActiveRecord query with alias'd table names
问题描述
使用模型的关注，其中包括示波器，什么是写这些明知嵌套和/或自引用查询可能最好的方法是什么？
在我关注的问题之一，我有范围类似这些：
范围：当前， - ＆GT;（as_at = Time.now）{current_and_expired（as_at）.current_and_future（as_at）}
适用范围： - {（？＃{upper_bound_column}为空或＃{upper_bound_column}＆GT; =，as_at）其中} current_and_future，＆GT（as_at = Time.now）
适用范围：current_and_expired， - ＆GT;（as_at = Time.now）{在这里（？＃{lower_bound_column}为空或＃{lower_bound_column}＆LT; =，as_at）}
高清self.lower_bound_column
lower_bound_field
结束
高清self.upper_bound_column
upper_bound_field
结束
和通过的has_many的，例如被称为：的has_many：company_users， - ＆GT; {当前}
如果一个ActiveRecord查询时其指几个模型，包括的关注，这会导致一个不明确的列名例外，这是很有意义的。
要帮助解决这个问题，我改列名辅助方法现在是
高清self.lower_bound_column
＃{self.table_name}＃{lower_bound_field}
结束
高清self.upper_bound_column
＃{self.table_name}＃{upper_bound_field}
结束
伟大的工程，直到你需要自引用查询。阿雷尔有助于在生成的SQL别名表名缓解这些问题，例如：
LEFT OUTER JOINcompany_userscompany_users_companiesONcompany_users_companies。的company_id=公司，ID
和
INNER JOINcompany_usersON用户，ID=company_users。user_ID的WHEREcompany_users。的company_id= $ 2
这里的问题是， self.table_name 不再是指在查询中的表名。而这将导致舌头在脸颊提示：提示：也许你的意思是引用表的别名company_users_companies
在尝试这些查询交给阿雷尔迁移，我改变了列名辅助方法：
高清self.lower_bound_column
self.class.arel_table [lower_bound_field.to_sym]
结束
高清self.upper_bound_column
self.class.arel_table [upper_bound_field.to_sym]
结束
和更新的范围，以反映：
lower_bound_column.eq（无）。或（lower_bound_column.lteq（as_at））
但这只是因为对面 self.class.arel_table 总是会考虑查询的是相同的移植问题。
我想我的问题是，如何创建范围，可以在自引用查询，这需要运营商，如＆LT使用; = 和＆GT; =
编辑
我创建了一个基本的应用程序，以帮助展示这个问题。
git的克隆git@github.com：fattymiller / expirable_test.git
CD expirable_test
CREATEDB expirable_test发展
安装捆绑包
耙分贝：迁移
耙分贝：种子
轨小号
的发现和假设
- 工作在sqlite3的，而不是Postgres的。最有可能的，因为Postgres的强制执行查询的SQL命令？
好，好，好。经过一个相当大的时间翻翻阿雷尔，的ActiveRecord 的来源和的Rails 的问题（看来这是不是新的），我能找到进入电流 arel_table 对象，其 table_aliases方式如果正在使用它们，在电流范围在其执行的时刻。
这成为可能知道的范围将是在加入有表名别名，或在另一方面，可以使用范围使用在真正的表名。
我只是说这个方法来你的可过期关注：
高清self.current_table_name
current_table = current_scope.arel.source.left
案例current_table
当阿雷尔::表
current_table.name
当阿雷尔::节点:: TableAlias
current_table.right
其他
失败
结束
结束
正如你所看到的，我使用的<一个href="http://apidock.com/rails/ActiveRecord/Scoping/ClassMethods/current_scope"><$c$c>current_scope为基本对象看，而不是使用 self.class.arel_table ，甚至事先尝试了AREL表， relation.arel_table ，它就像你说的仍然是相同的，无论在哪里范围内使用。我打电话源的对象上，以获取<一href="http://www.rubydoc.info/github/rails/arel/Arel/SelectManager"><$c$c>Arel::SelectManager这反过来会给你的 #left 当前表。这时有两种选择：你必须有一个阿雷尔::表（没有别名，表名是＃NAME ），或者你有一个阿雷尔::节点:: TableAlias 与别名的 #right 。
随着中table_name的，你可以恢复到＃{current_table_name}你的第一次尝试。＃{lower_bound_field} 和＃{current_table_name}＃{ upper_bound_field} 在你的领域：
高清self.lower_bound_column
＃{current_table_name}＃{lower_bound_field}
结束
高清self.upper_bound_column
＃{current_table_name}＃{upper_bound_field}
结束
适用范围： - {（？＃{upper_bound_column}为空或＃{upper_bound_column}＆GT; =，as_at）其中} current_and_future，＆GT（as_at = Time.now）
适用范围：current_and_expired， - ＆GT;（as_at = Time.now）{在这里（？＃{lower_bound_column}为空或＃{lower_bound_column}＆LT; =，as_at）}
本 current_table_name 方法，在我看来，是东西，将是对AR /阿雷尔公共API是有用的，所以它可以保持跨版本升级。你怎么看？
如果您有兴趣，这里有一些参考我以前在路上：
- 系统类似的SO 的问题，一吨的回答code，你可以使用，而不是你的美丽和简洁的能力。
- 在此 Rails的问题这的另一个。
- 和<一href="https://github.com/fattymiller/expirable_test/commit/b3380c80b22ffd481962bb61c74ab7b629adbca4">the提交您的测试应用在GitHub上，使得测试的绿色！
Using model concerns which include scopes, what is the best way to write these knowing that nested and/or self-referencing queries are likely?
In one of my concerns, I have scopes similar to these:
scope :current, ->(as_at = Time.now) { current_and_expired(as_at).current_and_future(as_at) }
scope :current_and_future, ->(as_at = Time.now) { where("#{upper_bound_column} IS NULL OR #{upper_bound_column} >= ?", as_at) }
scope :current_and_expired, ->(as_at = Time.now) { where("#{lower_bound_column} IS NULL OR #{lower_bound_column} <= ?", as_at) }
def self.lower_bound_column
lower_bound_field
end
def self.upper_bound_column
upper_bound_field
end
And is referred to via has_many's, example: has_many :company_users, -> { current }
If an ActiveRecord query is made which refers to a few models that include the concern, this results in an 'ambiguous column name' exception which makes sense.
To help overcome this, I change the column name helper methods to now be
def self.lower_bound_column
"#{self.table_name}.#{lower_bound_field}"
end
def self.upper_bound_column
"#{self.table_name}.#{upper_bound_field}"
end
Which works great, until you require self-referencing queries. Arel helps mitigate these issues by aliasing the table name in the resulting SQL, for example:
LEFT OUTER JOIN "company_users" "company_users_companies" ON "company_users_companies"."company_id" = "companies"."id"
and
INNER JOIN "company_users" ON "users"."id" = "company_users"."user_id" WHERE "company_users"."company_id" = $2
The issue here is that self.table_name no longer refers to the table name in the query. And this results in the tongue in cheek hint: HINT: Perhaps you meant to reference the table alias "company_users_companies"
In an attempt to migrate these queries over to Arel, I changed the column name helper methods to:
def self.lower_bound_column
self.class.arel_table[lower_bound_field.to_sym]
end
def self.upper_bound_column
self.class.arel_table[upper_bound_field.to_sym]
end
and updated the scopes to reflect:
lower_bound_column.eq(nil).or(lower_bound_column.lteq(as_at))
but this just ported the issue across since self.class.arel_table will always be the same regardless of the query.
I guess my question is, is how do I create scopes that can be used in self-referencing queries, which require operators such as <= and >=?
Edits
I have created a basic application to help showcase this issue.
git clone git@github.com:fattymiller/expirable_test.git
cd expirable_test
createdb expirable_test-development
bundle install
rake db:migrate
rake db:seed
rails s
Findings and assumptions
- Works in sqlite3, not Postgres. Most likely because Postgres enforces the order of queries in the SQL?
Well, well, well. After quite a big time looking through the sources of Arel, ActiveRecord and Rails issues (it seems this is not new), I was able to find the way to access the current arel_table object, with its table_aliases if they are being used, inside the current scope at the moment of its execution.
That made possible to know if the scope is going to be used within a JOIN that has the table name aliased, or if on the other hand the scope can be used on the real table name.
I just added this method to your Expirable concern:
def self.current_table_name
current_table = current_scope.arel.source.left
case current_table
when Arel::Table
current_table.name
when Arel::Nodes::TableAlias
current_table.right
else
fail
end
end
As you can see, I'm using current_scope as the base object to look for the arel table, instead of the prior attempts of using self.class.arel_table or even relation.arel_table, which as you said remained the same regardless of where the scope was used. I'm just calling source on that object to obtain an Arel::SelectManager that in turn will give you the current table on the #left. At this moment there are two options: that you have there an Arel::Table (no alias, table name is on #name) or that you have an Arel::Nodes::TableAlias with the alias on its #right.
With that table_name you can revert to your first attempt of #{current_table_name}.#{lower_bound_field} and #{current_table_name}.#{upper_bound_field} in your scopes:
def self.lower_bound_column
"#{current_table_name}.#{lower_bound_field}"
end
def self.upper_bound_column
"#{current_table_name}.#{upper_bound_field}"
end
scope :current_and_future, ->(as_at = Time.now) { where("#{upper_bound_column} IS NULL OR #{upper_bound_column} >= ?", as_at) }
scope :current_and_expired, ->(as_at = Time.now) { where("#{lower_bound_column} IS NULL OR #{lower_bound_column} <= ?", as_at) }
This current_table_name method seems to me to be something that would be useful to have on the AR / Arel public API, so it can be maintained across version upgrades. What do you think?
If you are interested, here are some references I used down the road:
- A similar question on SO, answered with a ton of code, that you could use instead of your beautiful and concise Ability.
- This Rails issue and this other one.
- And the commit on your test app on github that made tests green!
这篇关于ActiveRecord的查询与alias'd表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
