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

migrations.rb

class CreateProjects < ActiveRecord::Migration
  def change
    create_table :projects do |t|
      t.string :name
      t.timestamps
    end
  end
end


class CreateTasks < ActiveRecord::Migration
  def change
    create_table :tasks do |t|
      t.string :type
      t.references :project, index: true
      t.string :name
      t.timestamps
    end
  end
end


class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :type
      t.references :project, index: true
      t.string :name
      t.timestamps
    end
  end
end

models.rb

# all models

class Project < ActiveRecord::Base
  has_many :users
  has_many :students
  has_many :teachers
  has_many :tasks
  has_many :stories
end


class Task < ActiveRecord::Base
  belongs_to :project
end


class Story < Task
end


class User < ActiveRecord::Base
  belongs_to :project
end


class Student < User
end


class Teacher < User
end

quering.rb

# CASE ONE (single STI model include)

# This examples works:

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

Project.all.joins(: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')

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


# CASE TWO (double STI model include with same base model)

# This examples works:

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

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

# 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')
Project.all.includes(:students, :stories).order('stories.name ASC')

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

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

See question on stack overflow: http://stackoverflow.com/questions/28595636/rails-4-how-to-give-alias-names-to-includes-and-joins-in-active-record-que

- Model Student and model Teacher are both STI models with super class model User
- Model Story is a STI model with super class model Task
- includes() and joins(), both fails

Rails alias naming convention (includes() and joins())

- One model as parameter
  - is base model (includes(:users))
    -> alias name is class name of base name (in plural)
  - is STI model (includes(:students))
    -> alias name is class name of base name (in plural)

- More models as parameters
 - have more STI models with same base class name (includes(:students, :teachers))
   - first parameter (STI)      -> alias name is class name of base name (in plural)
   - all other parameters (STI) -> alias name is {STI name plural}_projects
 - One base model and one STI model with same base model (includes(:users, :teachers))
   - first parameter (base)     -> alias name is class name of base name (in plural)
   - second parameter (STI)     -> alias name is {STI name plural}_projects
 - More STI models with different base model (includes(:students, :stories))
   -> alias name is class name of base name (in plural)

The alias name depends on the parameter order in includes() or joins() and if the parameters have another parameter with same base class.

The test app:

- https://github.com/phlegx/rails_query_alias_names

Some links:

- https://github.com/rails/rails/issues/606
- https://github.com/rails/rails/blob/v4.2.0/activerecord/lib/active_record/associations/alias_tracker.rb#L59
- http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables
- https://rails.lighthouseapp.com/projects/8994/tickets/5617-patch-sti-join-model-polymorphic-associations-works-now
- https://groups.google.com/forum/#!topic/rails-oceania/iHlJPSd-lKM
- http://stackoverflow.com/questions/15789145/how-to-join-on-subqueries-using-arel

- https://github.com/rails/rails/blob/master/guides/bug_report_templates/active_record_gem.rb

seeds.rb

p1 = Project.find_or_create_by!(name: 'First Project')
p2 = Project.find_or_create_by!(name: 'Second Project')
p3 = Project.find_or_create_by!(name: 'Third Project')


u1 = User.find_or_create_by!(name: 'First User') do |u|
  u.project = p1
end
u2 = User.find_or_create_by!(name: 'Second User') do |u|
  u.project = p1
end
u3 = User.find_or_create_by!(name: 'Third User') do |u|
  u.project = p2
end
u4 = User.find_or_create_by!(name: 'Fourth User') do |u|
  u.project = p2
end
u5 = User.find_or_create_by!(name: 'Fifth User') do |u|
  u.project = p3
end


s1 = Student.find_or_create_by!(name: 'First Student') do |s|
  s.project = p1
end
s2 = Student.find_or_create_by!(name: 'Second Student') do |s|
  s.project = p1
end
s3 = Student.find_or_create_by!(name: 'Third Student') do |s|
  s.project = p2
end
s4 = Student.find_or_create_by!(name: 'Fourth Student') do |s|
  s.project = p2
end
s5 = Student.find_or_create_by!(name: 'Fifth Student') do |s|
  s.project = p3
end


t1 = Teacher.find_or_create_by!(name: 'First Teacher') do |t|
  t.project = p1
end
t2 = Teacher.find_or_create_by!(name: 'Second Teacher') do |t|
  t.project = p1
end
t3 = Teacher.find_or_create_by!(name: 'Third Teacher') do |t|
  t.project = p2
end
t4 = Teacher.find_or_create_by!(name: 'Fourth Teacher') do |t|
  t.project = p2
end
t5 = Teacher.find_or_create_by!(name: 'Fifth Teacher') do |t|
  t.project = p3
end


st1 = Story.find_or_create_by!(name: 'First Story') do |st|
  st.project = p1
end
st2 = Story.find_or_create_by!(name: 'Second Story') do |st|
  st.project = p1
end
st3 = Story.find_or_create_by!(name: 'Third Story') do |st|
  st.project = p2
end
st4 = Story.find_or_create_by!(name: 'Fourth Story') do |st|
  st.project = p2
end
st5 = Story.find_or_create_by!(name: 'Fifth Story') do |st|
  st.project = p3
end


ta1 = Task.find_or_create_by!(name: 'First Task') do |ta|
  ta.project = p1
end
ta2 = Task.find_or_create_by!(name: 'Second Task') do |ta|
  ta.project = p1
end
ta3 = Task.find_or_create_by!(name: 'Third Task') do |ta|
  ta.project = p2
end
ta4 = Task.find_or_create_by!(name: 'Fourth Task') do |ta|
  ta.project = p2
end
ta5 = Task.find_or_create_by!(name: 'Fifth Task') do |ta|
  ta.project = p3
end

tests.rb

# Working example

Project.joins('INNER JOIN "users" AS "students" ON "students"."project_id" = "projects"."id" AND "students"."type" IN ("Student")').order('students.name DESC')


# Try to transform query to Arel

s = Arel::Table.new(:users, as: 'students')
Project.joins(p.join(s).join_sources)


Project.joins(Project.arel_table.join(Student.arel_table).on(Student.arel_table[:project_id].eq(Project.arel_table[:id]).and(Student.arel_table[:type].in('Student'))).join_sources).to_sql


p = Arel::Table.new(:projects)
s = Arel::Table.new(:users, as: 'students')
Project.joins(p.join(s).on(p[:id].eq(s[:project_id]).and(s[:type].in('Student'))).join_sources)

Thanks 👍 Really useful.
Is it possible to force JOIN alias? It would be really useful for dynamically created queries

Then these two would work:

Project.all.joins(:students, :teachers).order('teachers_projects.name ASC') # fails
Project.all.joins(:students, :teachers).order('students_projects.name ASC') # works