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.

Jackson

your question is a little confusing but i think i know what you'd like to do. change the valid scope to joins(:user).where("(valid_from IS NULL OR valid_from < :now) AND (valid_until IS NULL OR valid_until > :now)", now: Time.zone.now).where(users: { active: true, or: something }) – Boilermaker

Answer 1

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!

Jackson

Answer 2

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.

Soubise

Looks like a really nice solution - I'll definitely give it a try – Jackson

Answer 3

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.

Dillie

This is great! I wanted the table name (string) instead of the actual Arel table, so I ended up with (current_scope ? current_scope.table : arel_table).name – Imre