ActiveRecord query with alias'd table names

activerecord, arel, ruby-on-rails

Solution

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!

Problem

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 git@github.com:fattymiller/expirable_test.git cd expirable_test createdb expirable_test-development bundle install rake db:migrate rake db:seed rails s ``` Findings and assumptions - Works in sqlite3, not Postgres. Most likely because Postgres enforces the order of queries in the SQL?

Original source

Related problems