Re: Get objects sorted by time of last comment

On 2 tammi, 08:50, Vibhu Rishi <vibhu.ri...@gmail.com> wrote:
> 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 ?

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

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