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
^ is fine. Long week.
I may want to chain these scopes, which will give me the dreaded
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.
Now refactor the User model as follows:
I'll go into more detail on what it's actually doing once I get some time.
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
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
ActiveRecord::StatementInvalid: Mysql2::Error: Not unique table/alias:
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
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