How to change these SQL statements with multiple joins into ActiveRecord style queries?

Artists have_many :posts, Posts have_many :comments. Artists follow other artists that they like through "inspirations". (I want to build a facebook-type news-feed to show recent activity such as:)

• someone has posted a new comment on one of my posts
• artist whom I follow has added a new post
• artist whom I follow received a new comment on a particular post

My idea is to do the 3 independent queries above, and concatenate the final messages by desc date. Here is how I get the 3 queries, using methods in my Artist class:

 def comments_on_my_posts
   Comment.where(:joins => :post, :conditions => {:posts => {:artist_id => 1}}).order("comments.updated_at DESC")
 end

 def posts_from_my_follows
   #SELECT * FROM posts inner join inspirations on posts.artist_id = inspirations.author_id where inspirations.artist_id = self.id
   #
 end

 def comments_on_posts_on_people_i_follows
   # SELECT * FROM comments inner join posts on comments.post_id = posts.id inner join inspirations on posts.artist_id = inspirations.author_id where inspirations.artist_id = self.id
 end

I prefer not to use SQL directly if I don't have to. But I don't know how to do multiple joins using ActiveRecord.

For reference, here are my models:

class Artist < ActiveRecord::Base

  has_many :posts, :dependent => :destroy, :order => 'updated_at DESC'
  has_many :inspirations
  has_many :follows, :through => :inspirations, :source => :inspiration

end


class Post < ActiveRecord::Base

  belongs_to :artist
  has_many :comments, :dependent => :destroy, :order => 'updated_at DESC' 

end

class Comment < ActiveRecord::Base

  belongs_to :author, :class_name => "Artist", :foreign_key => :author_id
  belongs_to :post

end

class Inspiration < ActiveRecord::Base

  #the follower
  belongs_to :artist

  #the followee
  belongs_to :inspiration, :class_name => 'Artist', :foreign_key => :author_id

  validate :disallow_self_referential_following

  validates_uniqueness_of :artist_id, :scope => :author_id

  def disallow_self_referential_following
    if artist_id == author_id
    errors.add(:author_id, 'cannot follow self')
  end
end开发者_运维知识库

The Rails 3 query interface for joins is documented here: http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables.

With Rails 3 you can chain your ActiveRecord calls together to build the query you want. Be sure to watch your development log file to see the queries that are being generated. (Unless you're on a Rails 3.1 version). You can also add a .to_sql call to the end of the query to see what it generates.