用条件连接同一个表两次

本文介绍了用条件连接同一个表两次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

我有一个多对多的关系:

模型表名称:users

第二个模型table_name:posts

加入表名:access_levels

一个帖子通过 access_levels 有很多用户，反之亦然.

User 模型和 Post 模型共享相同的关系:

has_many :access_levels, ->{ merge(AccessLevel.valid) }

AccessLevel 模型内部的作用域如下所示:

 # v1范围:有效，->{where((valid_from IS NULL OR valid_from  :now)", :now => Time.zone.now)}# v2# 范围:有效，->{# where("(#{table_name}.valid_from IS NULL OR #{table_name}.valid_from  Time.zone.now)# }

我想这样称呼某人:

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

ActiveRecord 为第二个连接创建一个别名('access_levels_users').我想在 AccessLevel 模型的有效"范围内引用此表名.

V1 显然会产生一个 PG::AmbiguousColumn-Error.V2 导致两个条件都以 access_levels. 为前缀，这在语义上是错误的.

这是我生成查询的方式:(简化)

# 策略内部范围 = 发布.加入(:access_levels).where(access_levels.level" => 1, access_levels.user_id" => current_user.id)# 在我的控制器里面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来自帖子"内部连接access_levels"ON access_levels".post_id"=帖子".id"AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-07-24 05:38:09.274104') AND ("access_levels"_un"valid";IS NULL OR access_levels".valid_until" > '2014-07-24 05:38:09.274132'))内部连接用户"ON用户".id"=access_levels".user_id"内部连接access_levels"access_levels_posts"开启access_levels_posts".post_id"=帖子".id"AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-07-24 05:38:09.274675') AND ("access_levels"_un"valid";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 范围用access_levels"而不是access_levels_posts"作为列的前缀.我也尝试使用arel来生成范围:

# v3范围:有效，->{其中 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)))}

结果查询保持不变.

解决方案

在此期间，我已经能够解决我自己的问题.我会发布我的解决方案，以帮助遇到类似问题的其他人.

序言:通往应许之地还有很长的路要走;)

我会尽量缩短设置:

## 设置#课后{合并(AccessLevel.valid)}has_many :users, :through =>:access_levels结尾类访问级别{其中 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)))}枚举:级别=>[:发布者，:订阅者]结尾类用户{合并(AccessLevel.valid)}has_many :users, :through =>:access_levels结尾

最初的目标是调用这样的东西(为了添加更多条件等):

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

这会导致语义错误的查询:

SELECT "posts".* FROM "posts"内连接access_levels"ON "access_levels"."post_id" = "posts"."id"AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-09-15 20:42:46.835548')AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:42:46.835688'))内部连接用户"ON "users"."id" = "access_levels"."user_id"内部连接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-09-15 20:42:46.836090')AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:42:46.836163'))

第二个连接使用别名 - 但条件未使用此别名.

Arel 来救援！

我已经使用裸区构建了以下所有连接，而不是信任 ActiveRecord.不幸的是，似乎将两者结合起来并不总是按预期工作.但至少它是这样工作的.我在这个例子中使用了外连接，所以无论如何我都必须自己构建它们.此外，所有这些查询都存储在策略中(使用 Pundit).所以它们很容易测试，并且没有胖控制器或任何冗余.所以我可以使用一些额外的代码.

## 我们的起点;)#范围 = 发布## 重建 `scope.joins(:users)` 或 `scope.joins(:access_levels => :user)`# 这里没有魔法.#join = Post.arel_table.join(AccessLevel.arel_table, Arel::Nodes::OuterJoin).on(Post.arel_table[:id].eq(AccessLevel.arel_table[:post_id]).and(AccessLevel.valid.where_values)).join_sourcesscope = scope.joins(join)join = AccessLevel.arel_table.join(User.arel_table, Arel::Nodes::OuterJoin).on(AccessLevel.arel_table[:user_id].eq(User.arel_table[:id])).join_sourcesscope = scope.joins(join)## 现在让我们在重用 AccessLevel.valid 范围的同时再次加入 access_levels 表.# 为了实现这一点，我们暂时交换了 AccessLevel.table_name#table_alias = 'al' # 这将是别名temporary_table_name = AccessLevel.table_name # 我们要稍后恢复原来的 table_nameAccessLevel.table_name = table_alias # 设置别名为table_namevalid_clause = AccessLevel.valid.where_values # 使用我们临时的 table_name 存储条件AccessLevel.table_name =temporary_table_name #恢复原来的table_name## 我们现在可以将 table_alias 与我们的 valid_clause 结合使用#join = Post.arel_table.join(AccessLevel.arel_table.alias(table_alias), Arel::Nodes::OuterJoin).on(Post.arel_table[:id].eq(AccessLevel.arel_table.alias(table_alias)[:post_id]).和(valid_clause)).join_sourcesscope = scope.joins(join)

经过所有的血汗和泪水之后，这是我们的查询结果:

SELECT "posts".* FROM "posts"左外连接access_levels"ON "posts"."id" = "access_levels"."post_id"AND ("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-09-15 20:35:34.420077')AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:35:34.420189')LEFT OUTER JOIN 用户"ON "access_levels"."user_id" = "users"."id"LEFT OUTER JOIN "access_levels" "al"ON "posts"."id" = "al"."post_id"AND ("al"."valid_from" IS NULL OR "al"."valid_from" < '2014-09-15 20:35:41.678492')AND ("al"."valid_until" IS NULL OR "al"."valid_until" > '2014-09-15 20:35:41.678603')

所有条件现在都使用正确的别名！

There are situations where ActiveRecord sets the alias table name if there are multiple joins with the same table. I'm stuck in a situation where these joins contain scopes (using 'merge').

I have a many-to-many relationship:

A Post has many users through access_levels and vice versa.

Both, the User model and the Post model share the same relation:

has_many :access_levels, -> { merge(AccessLevel.valid) }

The scope inside of the AccessLevel model looks like this:

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

I would like to call sth like this:

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

ActiveRecord creates an alias for the second join ('access_levels_users'). I want to reference this table name inside of the 'valid' scope of the AccessLevel model.

V1 obviously generates a PG::AmbiguousColumn-Error.V2 results in prefixing both conditions with access_levels., which is semantically wrong.

This is how I generate the query: (simplified)

# 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")

The generated query looks like this (using the valid scope v2 from above):

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 sets a propriate alias 'access_levels_posts' for the second join of the access_levels table.The problem is that the merged valid-scope prefixes the column with 'access_levels' instead of 'access_levels_posts'. I also tried to use arel to generate the scope:

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

The resulting query remains the same.

解决方案

I've been able to solve my own problem in the meantime. I'll post my solution to help others that are having similar issues.

Preamble: It's a long way to the promise land ;)

I'll keep the setup as short as possible:

#
# Setup
#
class Post < ActiveRecord::Base
  has_many :access_levels, -> { merge(AccessLevel.valid) }
  has_many :users, :through => :access_levels
end

class AccessLevel < ActiveRecord::Base
  belongs_to :post
  belongs_to :user

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

  enum :level => [:publisher, :subscriber]
end

class User < ActiveRecord::Base
  has_many :access_levels, -> { merge(AccessLevel.valid) }
  has_many :users, :through => :access_levels
end

The original goal was to call something like this (in order to add further conditions etc.):

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

That results in an semantically wrong query:

SELECT "posts".* 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-09-15 20:42:46.835548')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:42:46.835688'))

  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-09-15 20:42:46.836090')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:42:46.836163'))

The second join uses an alias - but the condition is not using this alias.

Arel to the rescue!

I've build all of the following joins with bare arel instead of trusting ActiveRecord. Unfortunately it seems that combining both is not always working as expected.But at least it's working at all that way. I'm using outer joins in this example so I'd have to build them by myself anyway. In addition all those queries are stored inside of policies (using Pundit). So they are easily testable and there's no fat controller or any redundancy. So I'm fine with some extra code.

#
# Our starting point ;)
#
scope = Post

#
# Rebuild `scope.joins(:users)` or `scope.joins(:access_levels => :user)`
# No magic here.
#
join = Post.arel_table.join(AccessLevel.arel_table, Arel::Nodes::OuterJoin).on(
  Post.arel_table[:id].eq(AccessLevel.arel_table[:post_id]).
  and(AccessLevel.valid.where_values)
).join_sources
scope = scope.joins(join)

join = AccessLevel.arel_table.join(User.arel_table, Arel::Nodes::OuterJoin).on(
  AccessLevel.arel_table[:user_id].eq(User.arel_table[:id])
).join_sources

scope = scope.joins(join)

#
# Now let's join the access_levels table for a second time while reusing the AccessLevel.valid scope.
# To accomplish that, we temporarily swap AccessLevel.table_name
#
table_alias            = 'al'                           # This will be the alias
temporary_table_name   = AccessLevel.table_name         # We want to restore the original table_name later
AccessLevel.table_name = table_alias                    # Set the alias as the table_name
valid_clause           = AccessLevel.valid.where_values # Store the condition with our temporarily table_name
AccessLevel.table_name = temporary_table_name           # Restore the original table_name

#
# We're now able to use the table_alias combined with our valid_clause
#
join = Post.arel_table.join(AccessLevel.arel_table.alias(table_alias), Arel::Nodes::OuterJoin).on(
  Post.arel_table[:id].eq(AccessLevel.arel_table.alias(table_alias)[:post_id]).
  and(valid_clause)
).join_sources

scope = scope.joins(join)

After all the blood, sweat and tears, here's our resulting query:

SELECT "posts".* FROM "posts"
  LEFT OUTER JOIN "access_levels"
    ON "posts"."id" = "access_levels"."post_id"
      AND ("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < '2014-09-15 20:35:34.420077')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > '2014-09-15 20:35:34.420189')

  LEFT OUTER JOIN "users"
    ON "access_levels"."user_id" = "users"."id"

  LEFT OUTER JOIN "access_levels" "al"
    ON "posts"."id" = "al"."post_id"
    AND ("al"."valid_from" IS NULL OR "al"."valid_from" < '2014-09-15 20:35:41.678492')
    AND ("al"."valid_until" IS NULL OR "al"."valid_until" > '2014-09-15 20:35:41.678603')

All conditions are now using a proper alias!

这篇关于用条件连接同一个表两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！