Get objects sorted by time of last comment

[Vibhu Rishi]

Hi All, 

A very happy new year to you all !

I am working on a website I am making as my hobby project. It is to do with motorcycle touring. 

I have done some initial work on it, and incrementally making changes as and when I can. 

I am trying to figure out the following issue : 

1. I have a forum object where people can start threads. 

2. the forum object uses the django comments module along with mptt. So far so good. 

3. Now, I want to show the "latest commented on" posts. But I am not able to figure it out. 

For reference : http://bikenomads.herokuapp.com/

On the box on the right, I want to show the posts based on the last comment time. However, all I can do right now is show the last post based on creation time (this is a field for the post object). I am not able to figure out how to sort based on comment time. 

Solutions :

1. Ideally there should be a way to sort object by comment time using the inbuilt comments module in django. Is this possible ? 

2. Alternatively, I will need to update the post model to have another field for 'last_comment_time' and when someone posts a comment, I will need to update this field. I would rather not do this as I will need to make sure all the objects using comments will need to have this exact field. 

What would you suggest ? 

Vibhu

--
Simplicity is the ultimate sophistication. - Leonardo da Vinci
Life is really simple, but we insist on making it complicated. - Confucius

[akaariai]

The ORM doesn't offer a way to do the query you want. The query will
be something like this:
select * from post
left join comment on comment.object_id = post.id and
comment.object_type = 'post'
and comment.timestamp = (select max(timestamp) from
comment
where object_id = post.id and
comment.object_type = 'post'
)
order by comment.timestamp;
(Assuming unique comment timestamp per post).

There are two underlying problems. First, Django's ORM doesn't
currently offer any way to generate more than single column equality
comparison in the JOIN clause. We are working on removing this
limitation from 1.6 (actually, the underlying limitation is already
somewhat gone). Second, the SQL needed isn't exactly nice. There are
multiple ways to write the same SQL, and how they perform differ
somewhat depending on the used DB and the amount of data.

So, what can you do pre 1.6? One way is to use views and some hackery
to do what you want.

class LatestComment(models.Model):
post = models.OneToOneField(Post, primary_key=True,
on_delete=models.DO_NOTHING, related_name='latest_comment')
{{ duplicate the columns in comment model here - you don't need
content type id }}

class Meta:
managed = False
db_table = 'latest_post_comment_view'

Then, create a view like this in the DB:

create or replace view "latest_post_comment_view" as (
select object_id as post_id, ...
from comment
where object_type = 'post'
group by post_id, ...
having max(timestamp) = timestamp
);

The SQL above is untested. In any case, you should now be able to do:

Post.objects.select_related('latest_comment').order_by('latest_comment__timestamp')

You will need to repeat the above for all the models with comments

Managing the raw SQL needed for the views can be somewhat ugly. The
last_comment_time field might be easier to implement & maintain. That
being said I have been using the above technique successfully in
production systems.

I do wish Django will one day have latest_related() functionality. I
find I need that often, and as the above shows this isn't easily
doable currently.

- Anssi

[Vibhu Rishi]

Hi Anssi, 

Thanks for the comment. 

I was thinking that if I have to do the last comment hackery for all the objects would it also work if I 

* add a last_comment_time field to the models I use with comments. 

* modify the comment form ( not sure which file to modify, still thinking it through) to add comment timestamp to the post model data. 

then the query could become much simpler in the sense that I only need to get post objects, and sort by last_comment_time field. 

Vibhu

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Victor Rocha]

Hi Vibhu,

You have more than one option, I think:

1)Modify model's Meta class and add an order_by = ('-comment__creation_date',)

2)Make a view that does just that last_threads_commented = Thread.comments.order_by('-comment__creation_date')

3)Make a custom template tag that does all of the heavy lifting behind the scenes.

disclaimer: I am not really sure if you can employed any of the methods listed here on a Generic Model tho, use at your own risk...

thank you,

Victor Rocha

RochApps

[akaariai]

On 5 tammi, 06:41, Vibhu Rishi <vibhu.ri...@gmail.com> wrote:
> Hi Anssi,
>
> Thanks for the comment.
>
> I was thinking that if I have to do the last comment hackery for all the
> objects would it also work if I
> * add a last_comment_time field to the models I use with comments.
> * modify the comment form ( not sure which file to modify, still thinking
> it through) to add comment timestamp to the post model data.
>
> then the query could become much simpler in the sense that I only need to
> get post objects, and sort by last_comment_time field.

This is likely the best approach for you. If you have an index on the
last_comment_time field then db queries will be really fast. This is
not the case for the view approach. In addition the view approach is
complex and somewhat hard to maintain while the last_comment_time
field is fairly straightforward to implement.

I wouldn't do this on the form level. Instead I would do this in
comment.save() (or post_save signal), or alternatively with a
dedicated method for comment saving. If every comment is saved through
that method then you know the last_comment_time will be correctly
maintained. Of course, there are a lot of ways to do this, and they
have different tradeoffs. Experiment and see what fits your purposes.

- Anssi