SQL error when joining same table using different associations

[graudeejs]

Given:

class Admin < ActiveRecord::Base
  # attribute name
  # attribute id
end

class Reminder < ActiveRecord::Base
  belongs_to :creator, class_name: :Admin
  belongs_to :receiver, class_name: :Admin
  # attribute creator_id
  # attribute receiver_id
end

When executing:

Reminder.
  joins(ArelHelpers.join_association(Reminder, :creator, Arel::InnerJoin)).
  joins(ArelHelpers.join_association(Reminder, :receiver, Arel::InnerJoin)).
  to_sql

The output will be

ELECT `reminders`.* FROM `reminders` INNER JOIN `admins` ON `admins`.`id` = `reminders`.`creator_id` INNER JOIN `admins` ON `admins`.`id` = `reminders`.`receiver_id

Which will raise error

ActiveRecord::StatementInvalid: Mysql2::Error: Not unique table/alias: 'admins'

Had this on Rails 4.1.x if that matters

[camertron]

Hey @graudeejs thanks for the bug report :)

Unfortunately I think this one is going to be fairly difficult to fix. The problem lies with the fact that ActiveRecord doesn't automatically combine joins on the same table. ArelHelpers doesn't even have access to the entire list of joins at any point, so it can't introspect the current list and auto-merge them.

We will probably have to get a little cleverer (here are two possible solutions):

Give every join an alias:

SELECT * FROM `foo`
JOIN `bar` b ON `b`.`id` = `foo`.`bar_id`
JOIN `bar` bb ON `bb`.`field` = `foo`.`bar_field`

--OR--

Provide a mechanism in ArelHelpers for grouping joins:

Reminder.joins(
  ArelHelpers.join_associations do |associator|
    associator.join_association(Reminder, :creator, Arel::InnerJoin).and(
      associator.join_association(Reminder, :receiver, Arel::InnerJoin)
    )
  end
)

Thoughts?

[graudeejs]

The issue is truly complicated.

Here's how I imagine it:

reminders_alias = 'reminders'
receiver_table = Reminder.arel_table.alias(reminders_alias)

Reminder.
  joins(ArelHelpers.join_association(Reminder, :creator, Arel::InnerJoin)).
  joins(ArelHelpers.join_association(Reminder, :receiver, Arel::InnerJoin, alias: reminders_alias )).
  where(receiver_table[:name].eq('foo')).
  to_sql

I haven't checked if where statement like this (using Arel alias) will actually work in Rails.

Such solution still leaves room for problems, but at least we as programmers can do something about that.

I don't think there will be one truly universal solution unless rails is rewritten to be able to retrieve all kinds of information about joins and other stuff.

[camertron]

Ah I see, that makes sense @graudeejs. I like having the caller pass in the alias in case they need to use it later. Unfortunately doing Reminder.arel_table.alias(reminders_alias) is pretty verbose. Maybe I can come up with an alias helper to make that easier.

[graudeejs]

Oh, god. I have no time for this issue. Sorry