multiple joins using activerecord in rails

Question

I'm building a small twitter style microblogging service where users can follow other users and get a feed of their messages

I have the following models:

class Follow < ActiveRecord::Base
  belongs_to :follower, :class_name => "User"
  belongs_to :followee, :class_name => "User"
end

class User < ActiveRecord::Base
  has_many :follows,   :foreign_key => 'follower_id',
                       :class_name => 'Follow'
  has_many :followers, :through => :follows
  has_many :followed,  :foreign_key => 'followee_id',
                       :class_name => 'Follow'
  has_many :followees, :through => :followed
  has_many :messages
end

class Message < ActiveRecord::Base
  belongs_to :user
end

To get a feed for the current user, I want to perform the following SQL query:

SELECT * FROM follows JOIN users JOIN messages WHERE follows.follower_id = current_user.id AND follows.followee_id = users.id AND users.id = messages.user_id;

What is the correct ActiveRecord way of doing this?

Answer 1

Not sure what you're looking for, but here is my suggestion:

I assume that you have other purposes for that Follow class, otherwise I don't see the purpose of it.

The "correct way" (i.e. my completely subjective way) to do it would actually be something like this:

class User < ActiveRecord::Base
  has_and_belongs_to_many :followers, :foreign_key => 'followed_id', 
      :class_name => 'User', :association_foreign_key => 'follower_id', 
      :include => [:messages]
  has_and_belongs_to_many :follows, :foreign_key => 'follower_id', 
      :class_name => 'User', :association_foreign_key => 'followed_id'
  has_many :messages
end

class Message < ActiveRecord::Base
  belongs_to :user
end

Then create the following table:

create_table :users_users, :id => false do |t|
  t.integer :followed_id
  t.integer :follower_id
end

And you're set:

followed = User.find :first
follower = User.find :last

followed.followers << follower

followed.followers.first.messages
followed.followers.first.followers.first.messages # etc...

But from what I make it, you want to show all the messages from all the followers at the same time.

This should be possible to achieve by adding has_and_belongs_to_many :followed_messages, :foreign_key => 'follower_id', :class_name => 'Message', :association_foreign_key => 'followed_id' to the *User* class, but I don't know how correct that way would be. Or it might be possible to achieve with association extensions but there I can't really give any examples.

Update: By changing the :class_name, it will associate it with the Message.id, didn't think about that so it will not be correct in this way.

So the only "nice" option is to go through the User class like in the first example. The only other options I can see is either the association extensions (which I can't give you an example for) or perhaps using a finder statement.

 has_many :followed_messages, :class_name => 'Message',
  :finder_sql => 'select * from messages where user_id in(select followed_id from users_users where follower_id = #{id})'

You probably have to customize that sql statement to get everything to work, but at least you should get the picture :)

Answer 2

Keijro's arrangement would work better, though if you need the Follow table, then you can execute the SQL query you specified as follows:

Follow.all(:joins => { :messages, :users }, :conditions => { "follows.follower_id" => current_user.id, "follows.followee_id" => "users.id", "users.id" => "messages.user_id"} )