Why does Rails generate duplicate SQL conditions when using scopes with associations?

I have setup a model structure which allows different models to associate with a File model through a has_many ... :through ... association which is also polymorphic so that a File can belong to many different models and different Resources can have many Files.

The File model then belongs_to an Attachment model which is the actual file but I use the File model to keep track of different versions amongst other things. The Attachment model knows whether the file is an image or not.

Here is the basic model structure:

class Resource < ActiveRecord::Base
  has_many :file_associations, :as => :association
  has_many :files, :through => :file_associations
  ...
end

class FileAssociation < ActiveRecord::Base
  belongs_to :r_file
  belongs_to :association, :polymorphic => true
  ...
end

class File < ActiveRecord::Base
  has_many :file_associations
  belongs_to :attachment

  named_scope :images, lambda { { :include => :attachment,
                                  :conditions => "attachments.type = 'AttachmentImage'" } }
  ...
end

I then use this setup to retrieve all Files from a specific Resource that has an Attachment that is an image, like this:

@resource.files.images

When I check the SQL query generated from this it has included that condition to join the Resource with the FileAssociation twice:

SELECT ....
FROM `files`
LEFT OUTER JOIN `attachments` ON `attachments`.id = `files`.attachment_id
INNER JOIN `file_associations` ON `files`.id = `file_associations`.file_id
WHERE
(
  ( `file_associations`.association_id = 1 ) AND
  ( `file_associations`.association_type = 'Resource' )
)
AND
(
  ( attachments.type = 'AttachmentImage' ) AND
  (
    ( `file_associations`.association_id = 1 ) AND
    ( `file_associations`.association_type = 'Resource' )
  )
)

If I try only calling @resource.files then the condition is not duplicated.

This works as intended of course, but judging by the query, it seems like I have done something that could be improved and I try to think about performance as much as I can. So why does this strange thing happen and what can I do to improve it?

For the record, rails 2.3.5 and mysql is used.

Update

I recently did a similar setup like the one described above, the only difference being that there was no polymorphic association, but when I looked at the query, still duplicate conditions. so that was not the cause of the problem.

Then I also tried removing lambda from the named_scope described above. I realized it was kinda unnecessary since I did not supply any argument. So the scope ended up looking like:

  named_scope :images, :include => :attachment, :conditions => "attachments.type = 'AttachmentImage'"

Still duplicate...

It might be time to open a ticket but I'm considering migrating to Rails 3 soon so I figured I can wait and see what will happen then.

Answers


Note that in rails 3.0 'named_scope' is deprecated in favor of simply 'scope'

I don't know why Rails doubles the condition, it may be a bug or an edge case.

Have you tried a second has_many association, something like this:

class Resource < ActiveRecord::Base
  has_many :file_associations, :as => :association
  has_many :files, :through => :file_associations
  has_many :images, :through => :file_associations,
                    :source => :file,
                    :include => :attachment,
                    :conditions => "attachments.type = 'AttachmentImage'"
  ...
end

Need Your Help

WPF window in front of another WPF window

wpf window z-order

I have a WPF-window, in this you can click a button to see a new window. Now I want to disable, that you can click the main-window. It should be like a MessageBox.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.