Word of Mike - Preventing Duplicate ActiveRecord Joins

Hi, welcome to Word of Mike, my little corner of the internet. I am a Software/Web Developer working in North Yorkshire. I mainly write about programming but my other passion is politics so beware. (click to hide)

2013-08-22 14:56:46 UTC

Preventing Duplicate ActiveRecord Joins

I've long thought rails does quite a poor job of handling multiple joins, it essentially just adds them all to an array and then uniq's it. So if you've joined twice in two scopes to :posts, it won't bomb out. If, however, you're dealing with string joins AND association (symbol) joins, then you're in for a rough ride.

Edit: nevermind, this is stupid, just alias the damned join tables

def self.some_scope
  .joins("LEFT OUTER JOIN alerts a ON a.incident_id = incidents.id").where(a: { foo: true })
end

def self.another_scope
  .joins(:alerts).where(alerts: { bar: true })
end

^ is fine. Long week.

I have an advanced scope which does various OUTER JOINS, which as we know have to be written in SQL, and lots of innocuous scopes which have association joins with symbols. For example:

class User < ActiveRecord::Base
  has_many :posts

  def self.posts_in_category(c)
    joins(posts: :category).where(categories: { id: c })
  end

  def self.something_more_complex
    joins("OUTER JOIN posts ...") #...
  end
end

I may want to chain these scopes, which will give me the dreaded

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

error. That's what you get when you join the same table twice. So, long story short I wrote a little work around, which works for me using MySQL and in every situation that I've happened to test it.

module UniqueJoins
  extend ActiveSupport::Concern

  module ClassMethods
    def unique_joins(*args)
      join_table_regex = /JOIN \W?(\w+)\W? ON/
      extract_join_strings_regex = /((?:(?:INNER|CROSS) |STRAIGHT_|(?:NATURAL )?(?:(?:LEFT|RIGHT)(?: OUTER)? ))?JOIN \W?\w+\W? ON \W?\w+\W?(?:.\W?\w+\W?)? = \W?\w+\W?(?:.\W?\w+\W?)?)/
      old_join_tables = scoped.to_sql.scan(join_table_regex).flatten
      missing_join_strings = []
      args.reject! do |j|
        join_string = joins(j).to_sql
        join_strings = Hash[join_string.scan(extract_join_strings_regex).map do |s|
          [s.first.scan(join_table_regex).flatten.first, s.first]
        end]
        new_join_tables = join_string.scan(join_table_regex).flatten
        missing_joins = (new_join_tables - old_join_tables)
        missing_join_strings += join_strings.values_at(*missing_joins)
        (new_join_tables & old_join_tables).any?
      end
      joins(args.concat(missing_join_strings))
    end
  end
end

Now refactor the User model as follows:

class User < ActiveRecord::Base
  includes UniqueJoins
  has_many :posts

  def self.posts_in_category(c)
    unique_joins(posts: :category).where(categories: { id: c })
  end

  def self.something_more_complex
    unique_joins("OUTER JOIN posts ...") #...
  end
end

I'll go into more detail on what it's actually doing once I get some time.

@mikecmpbll