Rails find: using conditions while including the same table using different named associations

Question

I have messages that users send to other users. There are two models: post and: user, and: post has the following associations:

belongs_to :from_user, :class_name => "User", :foreign_key => "from_user_id" belongs_to :to_user, :class_name => "User", :foreign_key => "to_user_id" 

Both: user and: post have an "is_public" column indicating that either one message or the entire user profile can be public or private.

My goal is to get a list of messages that are public and whose recipients have public profiles. At the same time, I would like to “include” the information of both the sender and the recipient in order to minimize the number of db calls. The problem is that I effectively "include" the same table twice through the named associations, but in my "conditions" I need to make sure that I only filter the recipient's is_public column.

I cannot do the following because the “conditions” do not accept the association name as a parameter:

 Post.find(:all, :include => [ :to_user, :from_user ], :conditions => { :is_public => true, :to_user => { :is_public => true }}) 

So, one of the ways I can do this is to make an extra "join" in the "users" table:

 Post.find(:all, :include => [ :to_user, :from_user ], :joins => "inner join users toalias on posts.to_user_id = toalias.id", :conditions => { :is_public => true, 'toalias.is_public' => true }) 

Is there a better, possibly cleaner way to do this?

Thanks in advance

Answer 1

I could not find a better solution than the one that was originally set out in my question. This does not depend on how the Rails name / alias name tables are compiled during the request and therefore seem cleaner than alternatives (except for using third-party gems or plugins):

 Post.find(:all, :include => [ :to_user, :from_user ], :joins => "inner join users toalias on posts.to_user_id = toalias.id", :conditions => { :is_public => true, 'toalias.is_public' => true }) 

Answer 2

I ran into the same problem and found a solution after looking at the SQL query generated from the rails query, the sql query automatically generates an alias try this,

 Post.find(:all, :include => [ :to_user, :from_user ], :conditions => { :is_public => true, 'to_users_posts.is_public' => true }) 

It worked for me :)

Answer 3

If you are on Rails 3, look at squeel gem if you often perform such complex joins. Or if you don't want to add an extra gem, check out the Arel table in Rails 3.

Answer 4

I really like this question because I tried to find the right solution for about 2 hours, so, after using a few tips above, I found a suitable solution in my case. My business: I need filter instances for the created_by_id / updated_by_id field, these fields are in each table, so ... what I did In the "Filterable" question, I wrote a method, and when I needed a filter for these fields, I used this →

key = "# {key.pluralize} _ # {name.pluralize.downcase} .email" if% w (created_by updated_by) .include? (key)

 # in case with invoices key = 'updated_bies_invoices.email' 

results = results.eager_load (: created_by ,: updated_by) .where ("# {key} =?", value)