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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.