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 On Wed, Jan 2, 2013 at 8:45 PM, akaariai <akaar...@gmail.com> wrote: > 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. > > -- Simplicity is the ultimate sophistication. - Leonardo da Vinci Life is really simple, but we insist on making it complicated. - Confucius -- 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.
