Rails Error: joins + include

Question

I have the following models:

class Person < ActiveRecord::Base
 has_many :images
 has_one :preference
end

class Image < ActiveRecord::Base
 belongs_to :person
end

class Preference < ActiveRecord::Base
 belongs_to :person
end

I am trying to fetch all images that are public and at the same time eager load the people who own those images:

    Image.find(:all, :conditions =>  ["images.person_id = ? AND preferences.image_privacy = ?", user.id, PRIVACY_PUBLIC],
               :joins => [:person => :user_preference], :include => :person)

It appears Rails does not like the :include (I believe because :person is referenced in 2 models). This is the error I get (which disappears when I drop the :include option):

"ActiveRecord::StatementInvalid: Mysql::Error: Not unique table/alias: 'people'"

I can get around this by writing out the actual JOIN command as a string and passing it into the :include option, but this not Rails-y so I was hoping there's a cleaner way to do this.

Any help would be much appreciated.

Thanks!

Answer 1

By using JOIN you are actively including the People table, so you shoudn't need to add "include" again. This should work:

  Image.find(:all, :conditions =>  ["images.person_id = ? AND preferences.image_privacy = ?", user.id, PRIVACY_PUBLIC],
           :joins => [:person => :user_preference])

Answer 2

It might be the issue with Table Aliasing, rails doc has great details in Table Aliasing

also, post SQL here will be useful too.

Answer 3

It looks like you call it "preferences", and not user_preferences. So you join should be:

:joins => [:person => :preference])

Answer 4

You wrote :conditions => ["images.person_id", user.id] and you said that you want to load images and people who owns those images. But it looks like you are loading images that belongs to one person (not to group of people), because you specify only one user.id.

I would do it this way:

Person.find(user.id, :include => [:images, :preference], :conditions => ["preferences.image_privacy = ?", PRIVACY_PUBLIC])

It will load person and his/her images.

Probably I don't understand your problem correctly, because what I think you want to do doesn't seem logic to me.

Instead of using conditions you can try named_scope