ActiveRecord query with alias'd table names

Question

Using model concerns which include scopes, what is the best way to write these knowing that nested and/or self-referencing queries are likely?

In one of my concerns, I have scopes similar to these:

scope :current, ->(as_at = Time.now) { current_and_expired(as_at).current_and_future(as_at) }
scope :current_and_future, ->(as_at = Time.now) { where("#{upper_bound_column} IS NULL OR #{upper_bound_column} >= ?", as_at) }
scope :current_and_expired, ->(as_at = Time.now) { where("#{lower_bound_column} IS NULL OR #{lower_bound_column} <= ?", as_at) }

def self.lower_bound_column
  lower_bound_field
end
def self.upper_bound_column
  upper_bound_field
end

And is referred to via has_many's, example: has_many :company_users, -> { current }

If an ActiveRecord query is made which refers to a few models that include the concern, this results in an 'ambiguous column name' exception which makes sense.

To help overcome this, I change the column name helper methods to now be

def self.lower_bound_column
  "#{self.table_name}.#{lower_bound_field}"
end
def self.upper_bound_column
   "#{self.table_name}.#{upper_bound_field}"
end

Which works great, until you require self-referencing queries. Arel helps mitigate these issues by aliasing the table name in the resulting SQL, for example:

LEFT OUTER JOIN "company_users" "company_users_companies" ON "company_users_companies"."company_id" = "companies"."id"

and

INNER JOIN "company_users" ON "users"."id" = "company_users"."user_id" WHERE "company_users"."company_id" = $2

The issue here is that self.table_name no longer refers to the table name in the query. And this results in the tongue in cheek hint: HINT: Perhaps you meant to reference the table alias "company_users_companies"

In an attempt to migrate these queries over to Arel, I changed the column name helper methods to:

def self.lower_bound_column
  self.class.arel_table[lower_bound_field.to_sym]
end
def self.upper_bound_column
  self.class.arel_table[upper_bound_field.to_sym]
end

and updated the scopes to reflect:

lower_bound_column.eq(nil).or(lower_bound_column.lteq(as_at))

but this just ported the issue across since self.class.arel_table will always be the same regardless of the query.

I guess my question is, is how do I create scopes that can be used in self-referencing queries, which require operators such as <= and >=?

---

Edits

I have created a basic application to help showcase this issue.

git clone [email protected]:fattymiller/expirable_test.git
cd expirable_test
createdb expirable_test-development
bundle install
rake db:migrate
rake db:seed
rails s

---

Findings and assumptions

1. Works in sqlite3, not Postgres. Most likely because Postgres enforces the order of queries in the SQL?

Answer 1

I have a slightly modified approach from @dgilperez, which uses the full power of Arel

def self.current_table_name
 current_table = current_scope.arel.source.left
end

now you could modify your methods with arel_table syntax

def self.lower_bound_column
 current_table[:lower_bound_field]
end

def self.upper_bound_column
  current_table[:upper_bound_field]
end

and use it query like this

 lower_bound_column.eq(nil).or(lower_bound_column.lteq(as_at))

Answer 2

Well, well, well. After quite a big time looking through the sources of Arel, ActiveRecord and Rails issues (it seems this is not new), I was able to find the way to access the current arel_table object, with its table_aliases if they are being used, inside the current scope at the moment of its execution.

That made possible to know if the scope is going to be used within a JOIN that has the table name aliased, or if on the other hand the scope can be used on the real table name.

I just added this method to your Expirable concern:

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

As you can see, 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, which as you said remained the same regardless of where the scope was used. I'm just 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.

With that table_name you can revert to your first attempt of #{current_table_name}.#{lower_bound_field} and #{current_table_name}.#{upper_bound_field} in your scopes:

def self.lower_bound_column
  "#{current_table_name}.#{lower_bound_field}"
end

def self.upper_bound_column
  "#{current_table_name}.#{upper_bound_field}"
end

scope :current_and_future, ->(as_at = Time.now) { where("#{upper_bound_column} IS NULL OR #{upper_bound_column} >= ?", as_at) }
scope :current_and_expired, ->(as_at = Time.now) { where("#{lower_bound_column} IS NULL OR #{lower_bound_column} <= ?", as_at) }

This current_table_name method seems to me to be something that would be useful to have on the AR / Arel public API, so it can be maintained across version upgrades. What do you think?

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.
• And the commit on your test app on github that made tests green!