SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    monitormensch oerdec's Avatar
    Join Date
    Sep 2004
    Location
    Hamburg
    Posts
    706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Forum app: Sorting topics by last post date

    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:

    Code Ruby:
    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:

    Code Ruby:
    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

  2. #2
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Presuming you have a field of "last_post_time" in your Topic model:

    Code Ruby:
      @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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2007
    Location
    Miami, FL
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:


    Code Ruby:
    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:

    Code Ruby:
    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.

    Code Ruby:
    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:

    Code Ruby:
    @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

    Code Ruby:
    @category.topics_count

    Instead of

    Code Ruby:
    @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.
    http://shownd.com - Your free online design portfolio.
    http://rehashclothes.com - Swap your clothes.
    http://orpheuxdesign.com - My design studio.

  4. #4
    monitormensch oerdec's Avatar
    Join Date
    Sep 2004
    Location
    Hamburg
    Posts
    706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code Ruby:
    :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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •