Join the same table twice with conditions

Question

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:

Models table_name: users

Second models table_name: posts

Join table name: access_levels

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.

Answer 1

After looking closer at this problem on a similar question here, I came up with a simpler and cleaner (to my eyes) solution to this question. I'm pasting here the relevant bits of my answer of the other question for completeness, along with your scope.

The point was to find a way to access the current arel_table object, with its table_aliases if they are being used, inside the scope at the moment of its execution. With that table, you will be able to know if the scope is being used within a JOIN that has the table name aliased (multiple joins on the same table), or if on the other hand the scope has no alias for the table name.

# 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

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. I'm 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.

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.

Answer 2

I came across this question when searching for things like this. I know it's a late answer, but if somebody else stumbles in here perhaps this can be to some help. This works in Rails 4.2.2, perhaps this couldn't be done when the question was made.

This answer was inspired by the answer from @dgilperez, but a bit simplified. Also using correct scope. So, here it is.

class Post < ActiveRecord::Base
  # the scope of the used association must be used
  has_many :access_levels, -> { merge(AccessLevel.valid(current_scope)) }
  has_many :users, :through => :access_levels
end

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

  # have an optional parameter for another scope than the scope of this class
  scope :valid, ->(cur_scope = nil) {
    # 'current_scope.table' is the same as 'current_scope.arel.source.left',
    # and there is no need to investigate if it's an alias or not.
    ar_table = cur_scope && cur_scope.table || arel_table
    now = Time.zone.now
    where(
      ar_table[:valid_from].eq(nil).or(ar_table[:valid_from].lt(now)).and(
      ar_table[:valid_until].eq(nil).or(ar_table[:valid_until].gt(now)))
    )
  }

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

class User < ActiveRecord::Base
  # the scope of the used association must be used
  has_many :access_levels, -> { merge(AccessLevel.valid(current_scope)) }
  has_many :users, :through => :access_levels
end

And no need to have it in two joins

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

I saw that you also changed to using OUTER JOINs, you can get that with:

Post.includes(:users, :access_levels).references(:users, :access_levels).first

But be aware using includes isn't always using one SQL request.