Rails model relations depending on count of nested relations

Question

I am putting together a messaging system for a rails app I am working on. I am building it in a similar fashion to facebook's system, so messages are grouped into threads, etc.

My related models are:

• MsgThread - main container of a thread
• Message - each message/reply in thread
• Recipience - ties to user to define which users should subscribe to this thread
• Read - determines whether or not a user has read a specific message

My relationships look like

class User < ActiveRecord::Base
    #stuff...
    has_many :msg_threads, :foreign_key => 'originator_id' #threads the user has started
    has_many :recipiences
    has_many :subscribed_threads, :through => :recipiences, :source => :msg_thread #threads the user is subscribed to
end

class MsgThread < ActiveRecord::Base
    has_many :messages
    has_many :recipiences
    belongs_to :originator, :class_name => "User", :foreign_key => "originator_id"
end

class Recipience < ActiveRecord::Base
    belongs_to :user
    belongs_to :msg_thread
end

class Message < ActiveRecord::Base
    belongs_to :msg_thread
    belongs_to :author, :class_name => "User", :foreign_key => "author_id"
end

class Read < ActiveRecord::Base
   belongs_to :user
   belongs_to :message
end

I'd like to create a new selector in the user sort of like:

has_many :updated_threads, :through => :recipiencies, :source => :msg_thread, :conditions => {THREAD CONTAINS MESSAGES WHICH ARE UNREAD (have no 'read' models tying a user to a message)}

I was thinking of either writing a long condition with multiple joins, or possibly writing giving the model an updated_threads method to return this, but I'd like to see if there is an easier way first. Am I able to pass some kind of nested hash into the conditions instead of a string?

Any ideas? Also, if there is something fundamentally wrong with my structure for this functionality let me know! Thanks!!

Answer 1

While I would still appreciate input on better possibilities if they exist, this is what I have gotten working now:

class User < ActiveRecord::Base
    # stuff...
    def updated_threads
        MsgThread.find_by_sql("
            SELECT msg_threads.* FROM msg_threads
            INNER JOIN messages ON messages.msg_thread_id = msg_threads.id
            INNER JOIN recipiences ON recipiences.msg_thread_id = msg_threads.id
            WHERE (SELECT COUNT(*) FROM `reads` WHERE reads.message_id = messages.id AND reads.user_id = #{self.id}) = 0
            AND (SELECT COUNT(*) FROM recipiences WHERE recipiences.user_id = #{self.id} AND recipiences.msg_thread_id = msg_threads.id) > 0
        ")
    end
end

Seems to be working fine!

Also to check if a specific thread (and message) are read:

class Message < ActiveRecord::Base
    # stuff...
    def read?(user_id)
        Read.exists?(:user_id => user_id, :message_id => self.id)
    end
end

class MsgThread < ActiveRecord::Base
    # stuff...
    def updated?(user_id)
        updated = false
        self.messages.each { |m| updated = true if !m.read?(user_id)  }
        updated
    end
end

Any suggestions to improve this?

Answer 2

You might want to take a look at Arel, which can help with complex SQL queries. I believe (don't quote me) this is already baked into Rails3.

Answer 3

Add a named_scope to the MsgThread model:

class MsgThread < ActiveRecord::Base
  named_scope :unread_threads, lambda { |user|
    {
    :include => [{:messages=>[:reads]}, recipiencies],
    :conditions => ["recipiences.user_id = ? AND reads.message_id IS NULL",
                     user.id],
    :group => "msg_threads.id"
    }}    
end

Note: Rails uses LEFT OUTER JOIN for :include. Hence the IS NULL check works.

Now you can do the following:

MsgThread.unread_threads(current_user)

Second part can be written as:

class Message
  has_many :reads
  def read?(usr)
    reads.exists?(:user_id => usr.id)
  end
end

class MsgThread < ActiveRecord::Base
  def updated?(usr)
    messages.first(:joins => :reads, 
                   :conditions => ["reads.user_id = ? ", usr.id]
    ) != nil
  end
end