Rails 4 - how to give alias names to includes() and joins() in active record querying

Question

How can I give an alias name for e.g. includes()? Following is given:

User: active record model
Student: active record model, inherits from User (STI)
Teacher: active record model, inherits from User (STI)
Project: active record model

Here some examples:

FIRST CASE (more STI associations)

Project.all.includes(:students, :teachers).order('teachers_projects.name ASC') # order on teachers
Project.all.includes(:students, :teachers).order('users.name ASC') # order on students

Rails uses automatically alias name teachers_projects for :teachers in the SQL. How can I overwrite this, so that I can use alias name teachers instead of teachers_projects in the SQL? :students gets alias name users.

This examples fails:

Project.all.includes(:students, :teachers).order('teachers.name ASC')
Project.all.includes(:students, :teachers).order('students.name ASC')
Project.all.includes(:students, :teachers).order('students_projects.name ASC')

SECOND CASE (one STI association)

If I use only :students (without :teachers) in method includes(), Rails uses name alias of the STI base class name users (without _projects attached) for :students:

Project.all.includes(:students).order('users.name ASC') # order on students

This examples fails:

Project.all.includes(:students).order('students.name ASC')
Project.all.includes(:students).order('students_projects.name ASC')

QUESTION

Might exist something like:

Project.all.includes(:students).alias(students: :my_alias)

RAILS ALIAS TRACKER

https://github.com/rails/rails/blob/v4.2.0/activerecord/lib/active_record/associations/alias_tracker.rb#L59

TESTING APP

https://gist.github.com/phlegx/add77d24ebc57f211e8b

https://github.com/phlegx/rails_query_alias_names

Have you tried something like Project.joins(:teachers).where...... — Leon, Feb 19 '15 at 01:02
If you get the answer, please _notify_ me, so that I can also see the answer. — Arup Rakshit, Feb 19 '15 at 05:47
Can you also give a Gist to show the models definitions, migrations and some data.. So that I can set up, and try to do ? — Arup Rakshit, Feb 19 '15 at 06:09
Have a look at this answer from a related question: http://stackoverflow.com/a/25856700/488195. He is using arel to handle the aliases in a way that might help you. I'm posting my approach as an answer as well. — dgilperez, Mar 03 '15 at 00:01

score 3 · Answer 1 · edited May 23 '17 at 12:26

I'm going to take another approach to this issue: instead of trying to control the alias names on your queries with an .alias method, I'll let Rails / Arel handle that and just look the correct table name (aliased or not) up whenever there is need for it within a scope.

Add this helper method to your model, that you'd be able to call from an scope 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.

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

This uses current_scope as the base object to look for the 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. There are two options there: either you have there an Arel::Table (no alias, table name is on #name) or you have an Arel::Nodes::TableAlias with the alias on its #right.

Now you only need to use that on your order statements (untested):

Project.all.includes(:students, :teachers).order("#{current_table_name}.name ASC")
Project.all.includes(:students, :teachers).order("#{current_table_name}.name ASC")
Project.all.includes(:students, :teachers).order("#{current_table_name}.name ASC")

Related questions:

ActiveRecord query with alias'd table names (where I first used this approach).
Join the same table twice with conditions

I gave an answer in one of the questions you refer. You can simplify this a bit. `current_scope.table` is the same as `current_scope.arel.source.left` (perhaps not in Rails < 4.2.2?). And if you use the `"Arel syntax"` you don't have to get the bare table name. E.g. use it like this: `at = current_scope.table`, and then `at[:the_column_name].asc` and `Arel` do all for you. (perhaps you have to add a `.to_sql`, but I think that Rails will handle it for you) — 244an, Oct 09 '15 at 16:24

score 3 · Answer 2 · answered Aug 15 '16 at 09:47

Arel does actually have an alias method.

student_alias = Student.arel_table.alias(:my_student_table_alias)

caveat: this will require you to use even more handcrafted Arel and do the join manually. And the joins in Arel can get a bit complicated if you're not used to them.

student_alias_join = student_alias.create_on(
  Project.arel_table[:primary_key].eq(student_alias[:project_id])
)

Project.joins(
  Project.arel_table.create_join(
    student_alias, student_alias_join, Arel::Nodes::OuterJoin
  )
).order(...)

Something like this should do it. Of course putting this into some Class method with :my_student_table_alias as parameter would make it more tidy and reusable as this would look a bit messy in a controller.

Rails 4 - how to give alias names to includes() and joins() in active record querying

2 Answers2

Linked