Have you ever used relationship conditions in your model just to have them not work with :include, making its' use impossible?
First, the solution I came up with:
ActiveRecord::Associations::ClassMethods::JoinDependency::JoinAssociation.class_eval do
super(conditions).split(/and|AND/).map(&:strip).
map {|x| "."}.join(" AND ")
end
end
Pretty simple, eh? Luckily for me, sanitize_sql is delegated and super calls the delegated method just fine. What this does is create a version of the delegated sanitize_sql method that forces the use of fully qualified condition names. SO, when we see code like this:
has_many :document_links,
:conditions => "document_id is not null and deleted_at is null"
...what we actually get in the SQL for that condition is: "document_links.document_id is not null and document_links.deleted_at is null". This allows generation of a query that doesn't die when both sides of the join have a column named 'deleted_at', or whatever else ends up giving you problems.
3 comments:
Thanks for this, but perhaps you can include a brief example of the problem/error that this solves?
Mark, it looks like this is a solution for the following:
class Users < ActiveRecord::Base
# id, created_at
has_many :emails
end
class Email < ActiveRecord::Base
# id, created_at
belongs_to :user
end
User.find(:all, :include => :emails, :conditions => 'id > 5')
You'll get an error, because 'id' is ambiguous.
Not exactly. This applies only to join conditions. In my specific situation I was handed an application where instead of deleting data, 'deleted_at' is set to the date it was removed. I needed in my association conditions 'deleted_at is null', which is ambiguous in a join because multiple tables have a column named 'deleted_at'... eager loading with :include would fail on that condition.
class Document < ActiveRecord::Base
# id, name, location, deleted_at
has_many :document_links, :conditions => 'deleted_at is null'
end
class DocumentLink < ActiveRecord::Base
# id, person_id, document_id, deleted_at
belongs_to :document
belongs_to :person
end
# this would error out without the plugin
Document.find(:first, :include => :document_links)
This plugin solves this issue by changing the ambiguous names into fully qualified ones that include the table name (or alias, if included multiple times) in the join condition. This only solves the problem of ambiguous column names in a join.
The resulting SQL would be something like...
"FROM documents LEFT OUTER JOIN document_links ON documents.id = document_links.document_id AND document_links.deleted_at IS NULL"
I realize now re-reading my post that I wasn't even slightly descriptive. I'll rewrite the post when I have time.
Post a Comment