rails find: using conditions while including same table twice via different named associations

Question

I have posts which are sent by users to other users. There are two models - :post and :user, and :post has the following named 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 "is_public" column, indicating that either a single post and/or the entire user profile can be public or private.

My goal is to fetch a list of posts which are public AND whose recipients have public profiles. At the same time, I would like to "include" both sender and recipient info to minimize the # of db calls. The challenge is that I am effectively "including" the same table twice via the named associations, but in my "conditions" I need to make sure that I only filter by the recipient's "is_public" column.

I can't do the following because "conditions" does not accept an association name as a parameter:

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

So, one way I can accomplish this is by doing an additional "join" on 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, perhaps cleaner, way to do this?

Thanks in advance

Answer 1

I was facing the same problem and found a solution after watching sql query generated from rails query, 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 2

I have not been able to find a better solution than the one originally stated in my question. This one doesn't depend on how Rails names/aliases tables when compiling a query and therefore appears to be cleaner than the alternatives (short of using 3rd 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 })