sql - 用条件连接同一个表两次

Question

如果同一个表有多个连接，ActiveRecord 会设置别名表名。我陷入了这些连接包含范围(使用“合并”)的情况。
我有一个多对多的关系:

Models table_name: users

Second models table_name: posts

Join table name: access_levels

一个帖子通过 access_levels 有很多用户，反之亦然。
User 模型和 Post 模型共享相同的关系: has_many :access_levels, -> { merge(AccessLevel.valid) }AccessLevel 模型内部的范围如下所示:

  # v1
  scope :valid, -> {
    where("(valid_from IS NULL OR valid_from < :now) AND (valid_until IS NULL OR valid_until > :now)", :now => Time.zone.now)
  }
  
  # v2
  # scope :valid, -> {
  #   where("(#{table_name}.valid_from IS NULL OR #{table_name}.valid_from < :now) AND (#{table_name}.valid_until IS NULL OR #{table_name}.valid_until > :now)", :now => Time.zone.now)
  # }

我想这样称呼某事:

Post.joins(:access_levels).joins(:users).where (...)

ActiveRecord 为第二个连接创建一个别名('access_levels_users')。我想在 AccessLevel 模型的“有效”范围内引用此表名。
V1 显然生成了 PG::AmbiguousColumn -错误。
V2 导致两个条件都带有 access_levels. 前缀，这在语义上是错误的。
这就是我生成查询的方式:(简化)

# inside of a policy
scope = Post.
  joins(:access_levels).
  where("access_levels.level" => 1, "access_levels.user_id" => current_user.id)

# inside of my controller
scope.joins(:users).select([
        Post.arel_table[Arel.star],
        "hstore(array_agg(users.id::text), array_agg(users.email::text)) user_names"
      ]).distinct.group("posts.id")

生成的查询如下所示(使用上面的 valid 范围 v2):

SELECT "posts".*, hstore(array_agg(users.id::text), array_agg(users.email::text)) user_names
  
  FROM "posts"
  INNER JOIN "access_levels" ON "access_levels"."post_id" = "posts"."id" AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-07-24 05:38:09.274104') AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-07-24 05:38:09.274132'))
  INNER JOIN "users" ON "users"."id" = "access_levels"."user_id"
  INNER JOIN "access_levels" "access_levels_posts" ON "access_levels_posts"."post_id" = "posts"."id" AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-07-24 05:38:09.274675') AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-07-24 05:38:09.274688'))

  WHERE "posts"."deleted_at" IS NULL AND "access_levels"."level" = 4 AND "access_levels"."user_id" = 1 GROUP BY posts.id

ActiveRecord 为 access_levels 表的第二个连接设置了一个适当的别名“access_levels_posts”。
问题是合并的 valid -scope 使用“access_levels”而不是“access_levels_posts”作为列的前缀。我还尝试使用 arel 来生成范围:

# v3
scope :valid, -> {
  where arel_table[:valid_from].eq(nil).or(arel_table[:valid_from].lt(Time.zone.now)).and(
    arel_table[:valid_until].eq(nil).or(arel_table[:valid_until].gt(Time.zone.now))
  )
}

结果查询保持不变。

Answer 1

在 similar question here 上仔细查看此问题后，我想出了一个更简单、更干净(在我看来)的解决方案来解决这个问题。为了完整起见，我在此处粘贴了我对另一个问题的回答的相关部分以及您的范围。

关键是要找到一种方法来访问当前 arel_table对象，及其 table_aliases如果正在使用它们，则在执行时在范围内。使用该表，您将能够知道范围是否在 JOIN 中使用。具有别名的表名(同一个表上的多个连接)，或者另一方面，如果范围没有表名的别名。

# based on your v2
scope :valid, -> {
  where("(#{current_table_from_scope}.valid_from IS NULL OR 
          #{current_table_from_scope}.valid_from < :now) AND 
         (#{current_table_from_scope}.valid_until IS NULL OR 
          #{current_table_from_scope}.valid_until > :now)", 
       :now => Time.zone.now) 
  }

def self.current_table_from_scope
  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 .

如果您有兴趣，以下是我在路上使用的一些引用资料:

similar question on SO ，用大量代码回答，你可以用它们代替你漂亮简洁的能力。

此 Rails issue而这个 other one .