If it were me I would simplify your query by adding a last_activity
field to your posts table (your option 3).
As far as updating this field I would avoid coupling the Post and
Comment models. I would take care of updating the last_activity on
post in the create action on the comments_controller.
You might also consider using an after_filter to update the field in
case other activities should also update the last_activity, such as
edits to comments using the update action on comments_controller.
Something like this:
after_filter update_last_activity, :only => [ :create, :update ]
def create
@comment = Comment.new(params[:comment])
...
...
...
end
protected
def update_last_activity
@comment.post.last_activity = Time.now
@comment.post.save # <--- I''m not sure if this is necessary
end
This is just one possible solution (totally untested) off the top of
my head. There may be better approaches, but I still believe the
last_activity column on Post is the most efficient way to do what you
want.
On Jun 5, 5:25 am, Tim <asm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Hi
>
> I am developing a forum style rails app (my first one). Everything
> has been wonderfully simple and clean so far. However I have got to
> the point, which seems inevitable whenever I use SQL, where a
> seemingly simple query threatens to derail me (no pun intended). Say
> I have this simple schema:
>
> create_table :posts do |t|
> t.column :user_id, :integer
> t.column :subject, :string
> t.column :body, :text
> t.column :created_at, :datetime
> end
>
> create_table :comments do |t|
> t.column :user_id, :integer
> t.column :body, :text
> t.column :created_at, :datetime
> end
>
> I want my forum post list to be ordered by ''last
activity'', which
> would be the time of a post or its last comment. This means that a
> new comment will move a post to the top of the list.
>
> This seems to be a simple problem without a simple solution. I would
> like to investigate all avenues since it seems that each will have its
> drawbacks. Here are the solutions I have been looking at:
>
> 1) Create a large, complicated query, using either aggregation or a
> subquery, to get the correct ordering of posts. Can''t think of
how to
> do this off the top of my head but it should be possible. Also what
> would the performance be like? If I have 50,000 posts would I still
> be using a raw query, or would I be caching queries, or would I be
> caching further up the rails stack?
>
> 2) Refactor my design. Remove posts.body and use a comment instead
> for the post body. This could be done either by making Post inherit
> from Comment, using a separate 1-1 relationship between Post and
> Comment for the post body, or using the first comment as the post
> body. Of theses solutions only the latter will simplify my listing
> query. Seems wrong to change my design for a query.
>
> 3) Add a last_activity column to posts. This would be a datetime. It
> would be initialized to Time.now() when a post is created, and updated
> when a new comment is added. Solves the query problem. The column is
> redundant though. What''s the best way to implement this? Seems
like
> it should be done in the model. Right now my comments are created
> like this:
>
> c = Comment.new(params[:comment])
> c.user = @current_user
> c.post = @post
> c.save
> ...
>
> Seems like I could put a callback in to the Comment model to update
> the last_activity column in the Post, but then I would have to call
> Post#save. Are there any implications to this? Also I understand
> that a comment could be associated with a Post by doing @post.comments
> << c. What would my last_activity code do then?
>
> Any thoughts would be greatly appreciated.
> TIA
> Tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---