Forum app: Sorting topics by last post date

[oerdec]

Hello all,

I'm currently working on a little forum app to learn rails.
In a category all topics have to be listed. How can I sort the topics by the date of the last post?

My models:

class Category < ActiveRecord::Base
  has_many :topics  
end
 
class Topic < ActiveRecord::Base
  belongs_to :category
  has_many :posts
end
 
class Post < ActiveRecord::Base
  belongs_to :topic
end

In my topics table there is a field last_post_id.

My controller:

class CategoryController < ApplicationController
  def show
    category_id = params[:id]
 
    @category = Category.find(category_id)
 
    sql = "topics.*, 
           (SELECT posts.created_at 
              FROM posts
             WHERE posts.id = topics.last_post_id)
           AS last_post_time"
    @topics = Topic.find(:all,
                         :select     => sql,
                         :conditions => {:category_id => category_id},
                         :order      => 'last_post_time DESC')
  end
end

That`s ugly. @category allready contains the all topics. Beside that I would like to avoid mixing SQL with Ruby. And I think such a query should be in a model.

Could anyone show me a better way?

oerdec

[lo0ol]

Presuming you have a field of "last_post_time" in your Topic model:

  @topics = @category.topics.find(:all, :order => 'last_post_time DESC')

And that's what I suggest. Technically you could go through all the posts, look at their last_post_time and float that up to the parent, but to me I don't think that's very worthwhile just to try to keep it normalized. If you let users sort 30 topics per page, each topic having 50 posts each, that's like, one metric crap ton of extra overhead than just adding one extra field to Topic that makes it one-query-able. You might not even need to add an extra field... just cram the last_post_time into a Topic's "updated_at" field and re-save the Topic when a new Post is submitted. That could make sense if you enjoy the idea that a Topic is "updated" if someone posts to it. You could also argue that that is a stupid idea. I think I might like the updated_at idea, but depends on the coder, I think.

[realmadrid2727]

I would suggest using a counter_cache which would knock out two birds with one stone; keep track of how many posts there are in a topic (and how many topics in a category) and use the power of Rails' automatic timestamping to keep track of the last post. Try this:

class Category < ActiveRecord::Base
  has_many :topics, :order => "updated_at DESC"
end
 
class Topic < ActiveRecord::Base
  belongs_to :category, :counter_cache => :true
  has_many :posts, :order => "created_at ASC"
end
 
class Post < ActiveRecord::Base
  belongs_to :topic, :counter_cache => :true
end

You'll also need to add an integer column named "topics_count" to your categories migration and another one named "posts_count" to your topics migration. Lastly, add an index to your topics and posts migrations:

class CreateTopics < ActiveRecord::Migration
  def self.up
    create_table :topics do |t|
	# Your columns
    end
	add_index :topics, :category_id
  end
 
  def self.down
    drop_table :topics
  end
end

Repeat for posts.

class CreatePosts < ActiveRecord::Migration
  def self.up
    create_table :posts do |t|
	# Your columns
    end
	add_index :posts, :topic_id
  end
 
  def self.down
    drop_table :posts
  end
end

From your controller you can do this:

@category = Category.find(params[:id])
@topics = @category.topics

They'll already be sorted by date of last post. The counter cache updates the "topics_count" column in the category table upon each create/destroy which in turn updates the "updated_at" timestamp which you're sorting by in the model.

The counter_cache also lets you know how many topics are in a category by using

@category.topics_count

Instead of

@category.topics.length

That gives you the added speed/efficiency bonus of not having to make an extra DB query. Same goes for the amount of posts in a topic.

[oerdec]

Thank you guys for your replies! Both were really helpfull.

@lo0ol: You are right, going through all posts of all topics in a category would be way too much overhead. Normally I would like to have a normalised schema - but sometimes it`s not worth it. Anyway, I tried realmadrid`s approach - and it works like a charm. I just had to do a minor correction:

:counter_cache => true
# instead of
:counter_cache => :true

Beside that the fields topics_count and posts_count need a default value of 0.

OK, thank you again.

Cheers

oerdec