How to add additional condition to the JOIN generated by include?

Question

I want to add additional criteria to the LEFT OUTER JOIN generated by the :include option in ActiveRecord finder.

class Post
  has_many :comments
end

class Comment
  belongs_to :post
  has_many   :comment_votes
end

class CommentVote
  belongs_to :comment
end

Now lets say I want to find last 10 posts with their associated comments and the up votes.

Post.find.all(:limit => 10, :order => "created_at DESC",
    :include => [{:comments => :comment_votes])

I cant add the condition to check for up votes as it will ignore the posts without the up votes. So the condition has to go the ON clause of the JOIN generated for the comment_votes. I am wishing for a syntax such as:

Post.find.all(:limit => 10, :order => "created_at DESC",
    :include => [{:comments => [:comment_votes, :on => "comment_votes.vote > 0"])

Have you faced such problems before? Did you managed to solve the problem using the current finder? I hope to hear some interesting ideas from the community.

PS: I can write a join SQL to get the expected result and stitch the results together. I want to make sure there is no other alternative before going down that path.

Answer 1

If I'm following your question correctly, something like this may work:

class Comment
  belongs_to :post
  has_many   :comment_votes
  has_many   :up_votes, :class_name => "CommentVote", :conditions => "vote > 0"
end

Then your finder would be:

Post.find.all(:limit => 10, :order => "created_at DESC",
:include => {:comments => :up_votes})

Note: I didn't test this.