带有别名表名的 ActiveRecord 查询 [英] ActiveRecord query with alias'd table names

问题描述

使用包含范围的模型问题，知道嵌套和/或自引用查询是可能的，编写这些问题的最佳方法是什么?

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

并且通过has_many的引用，例如:has_many :company_users, ->{ 当前}

And is referred to via has_many's, example: has_many :company_users, -> { current }

如果一个 ActiveRecord 查询引用了几个包含关注的模型，这会导致不明确的列名"异常，这是有道理的.

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

这很好用，直到您需要自引用查询.Arel 通过在生成的 SQL 中为表名添加别名来帮助缓解这些问题，例如:

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"

和

INNER JOIN "company_users" ON "users"."id" = "company_users"."user_id" WHERE "company_users"."company_id" = $2

这里的问题是 self.table_name 不再引用查询中的表名.这导致舌头在脸颊上提示:提示:也许您打算引用表别名company_users_companies"

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"

为了将这些查询迁移到 Arel，我将列名辅助方法更改为:

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

并更新范围以反映:

lower_bound_column.eq(nil).or(lower_bound_column.lteq(as_at))

但这只是移植了问题，因为无论查询如何，self.class.arel_table 将始终相同.

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 >=?

我创建了一个基本应用程序来帮助展示这个问题.

I have created a basic application to help showcase this issue.

git clone [e-mail]:fattymiller/expirable_test.git
cd expirable_test
createdb expirable_test-development
bundle install
rake db:migrate
rake db:seed
rails s

<小时>

发现和假设

1. 在 sqlite3 中工作，而不是 Postgres.很可能是因为 Postgres 强制执行 SQL 中的查询顺序?

推荐答案

好吧，好吧.在查看了 Arel、ActiveRecord 和 Rails 问题的来源(这似乎不是新的)之后，我能够找到访问当前 arel_table 对象的方法，以及它的 table_aliases(如果它们正在被使用)，在它执行时的 current 范围内.

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.

这样就可以知道范围是否将在具有别名表名的 JOIN 中使用，或者另一方面是否可以在真实表名上使用范围.

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.

我刚刚将此方法添加到您的 Expirable 关注点:

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

如您所见，我正在使用 current_scope 作为查找 arel 表的基础对象，而不是使用 self.class.arel_table 甚至 relation.arel_table 的先前尝试，正如您所说无论范围在哪里使用，都保持不变.我只是在那个对象上调用 source 来获取一个 Arel::SelectManager 反过来会给你在 #left 上的当前表.目前有两种选择:你有一个 Arel::Table(没有别名，表名在 #name 上)或者你有一个 Arel::Nodes::TableAlias 及其 #right 上的别名.

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.

使用该 table_name，您可以恢复第一次尝试 #{current_table_name}.#{lower_bound_field} 和 #{current_table_name}.#{upper_bound_field} 在您的范围内:

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) }

在我看来，这个 current_table_name 方法对 AR/Arel 公共 API 很有用，因此可以在版本升级时维护它.你怎么看?

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 关于 SO 的类似问题，回答了大量代码，你可以用它来代替你漂亮简洁的能力.
• 这个Rails 问题和这个其他.
• 还有 在您的测试应用上的提交在 github 上使测试变得绿色！莉>

• 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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！