有条件地联接同一张桌子两次

同时，我已经能够解决自己的问题。我将发布解决方案以帮助遇到类似问题的其他人。

序言：通往应许之地的路很长；）

我将使设置尽可能短：

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

最初的目标是调用这样的名称（以添加更多条件等）：

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

这导致语义错误的查询：

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

第二个联接使用别名-但条件不是使用该别名。

救急！

我已经使用裸露的竞技场建立了以下所有联接，而不是信任ActiveRecord。不幸的是，似乎两者都不总是能按预期进行。但是至少它一直都在工作。我在此示例中使用外部联接，因此无论如何我都必须自己构建它们。此外，所有这些查询都存储在策略内部（使用Pundit）。因此它们很容易测试，并且没有胖控制器或任何冗余。所以我可以添加一些额外的代码。

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

经过所有的血汗和眼泪，这是我们的结果查询：

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

现在所有条件都使用适当的别名！