gistfile1.rb

# forums_controller.rb

# in show:
        @topic_pages, @topics = paginate(:topics, :per_page => 23, :conditions => ['forum_id = ?', @forum.id], :order => 'sticky desc, replied_at desc', :select => 'topics.*, IF(unread_user_topics.user_id IS NOT NULL,1,0) AS unread, users.display_name AS last_reply_author', :joins => "LEFT JOIN unread_user_topics ON topics.id = unread_user_topics.topic_id AND unread_user_topics.user_id = #{current_user.id} LEFT JOIN users ON topics.replied_by = users.id")

# application_helper.rb

  # used to know if a topic has changed since we read it last
  def recent_topic_activity(topic)
    return false if not logged_in?
    return ActiveRecord::Base.connection.select_all("SELECT COUNT(topic_id) AS unread_count FROM unread_user_topics WHERE user_id = #{current_user.id} AND topic_id = #{topic.id}")[0]['unread_count'].to_i > 0
  end

# forums_helper.rb

  # used to know if a forum has changed since we read it last
  def recent_forum_activity(forum)
    return false unless logged_in?
    return ActiveRecord::Base.connection.select_all("SELECT COALESCE(COUNT(unread_user_topics.topic_id), 0) AS unread_count FROM topics LEFT JOIN unread_user_topics ON unread_user_topics.topic_id = topics.id AND unread_user_topics.user_id = #{current_user.id} WHERE topics.forum_id = #{forum.id}")[0]['unread_count'].to_i > 0
  end

# models/topic.rb

  after_save    :set_unread_status

  def set_unread_status
    self.connection.execute "INSERT IGNORE INTO unread_user_topics (user_id, topic_id) SELECT id, #{self.id} FROM users WHERE last_seen_at < '#{self.replied_at.to_formatted_s(:db)}'";
  end

  def mark_as_read_by(user)
    self.connection.execute "DELETE FROM unread_user_topics WHERE topic_id = #{id} AND user_id = #{user.id}";
  end

# models/post.rb

  # after a reply is saved, set topic as unread.
  after_create {|r| 
    # ...
    r.topic.set_unread_status
  }