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?

Beaudoin

I have a similar problem. See my question here: #28596136 – Whet

Answer 1

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!

Prim

Thank you so much for your help and effort with this issue! My research showed very similar suggestions - one even suggesting to parse the generated SQL :| I would think that there would be a reason that they have not included something similar to what you suggest, but it would definitely be worth a discussion for you to have with the Rails team to see if they would consider merging your solution into core - your name would be against a pretty nice chunk of code for future coders to see.. – Beaudoin

I'm glad to help and I can say I also learned a lot with this one! Quite a challange :) I'll ask on the Rails issue if they'd consider a PR. Thanks! – Prim

@dgilperez, Hey.. what do you think about my modified approach? I would love to hear your suggestions.. – Lassa

Answer 2

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

Lassa