Django - Sorting QuerySet of threaded comments

I am using django-threadedcomments, however the question also applies generally to sorting a QuerySet.

The comment objects in the QuerySet have two important fields, tree_path and submit_date. tree_path is of the form "a/b/.../z" where 'a' is the highest-order index in the tree, and 'b' is the lowest order index in the tree. So the first root comment will have a tree_path of '1'. A child of that comment will have a tree_path of '1/1'. Another child of '1' will have a tree_path of '1/2'. The second root comment will have a root_path of '2', etc...

The QuerySet "qs" is sorted like above, with comments in threaded order with the oldest comments on top. Just the tree_paths of the above example would look like [1, 1/1, 1/2, 2]. I would like to sort each level of comments 开发者_开发知识库with the newest comments first. So the QuerySet instead should be [2, 1, 1/2, 1/1].

How can I do this?

I can sort just the root level comments by using:

qs = qs.extra(select={ 'tree_path_root': 'SUBSTRING(tree_path, 1, 1)' })
       .order_by('%stree_path_root' % ('-'), 'tree_path')

But I cannot figure out how to sort the non-root comments at the same time. I've tried something like:

qs = qs.extra(select={ 'tree_path_root': 'SUBSTRING(tree_path, 1, 1)' 
                       'tree_path_sec' : 'SUBSTRING(tree_path, 3, 1)'})
       .order_by('%stree_path_root' % ('-'), '%stree_path_sec' % ('-'), 'tree_path')

But that destroys the threading of the comments.

Any suggestions? Thanks!

I realize this has been a little while since you posted.. so you may have an answer by now, or maybe you have moved on. Regardless, here you go... :)

You are misunderstanding the tree_path structure in the django-threadedcomments application. There will never be a tree_path of 1/1, as each path segment is the unique primary key of that ThreadedComment.

If you start with ThreadedComment 1, and add a reply, you will get a path of 1/2. Then if you add an additional top-level post, it would get the path 3. This would give you:

1
1/2
3

And if you reply to the first post again, you would get:

1
1/2
1/4
3

Now to address the sorting issue. I have attempted to do a similar sorting (by a voting score, similar to reddit), and found no easy way to do it. However, here is a recursive method that you can use: (It is ugly, and slow... but its a starting point)

def sort_comments(tree):
    final_tree = []
    root_comments = [c for c in tree if c.tree_path.count('/') == 0]
    root_comments.sort(key=lambda comment: comment.submit_date, reverse=True)
    for comment in root_comments:
        final_tree.append(comment)
        append_and_sort_children(final_tree, tree, comment)
    return final_tree


def append_and_sort_children(final_tree, tree, parent):
    children = [c for c in tree if c.parent_id == parent.id]
    children.sort(key=lambda comment: comment.submit_date, reverse=True)
    for comment in children:
        final_tree.append(comment)
        append_and_sort_children(final_tree, tree, comment)

Using this, simply pass in your entire query set of comments for that model, and python will sort them for you. :)

This will give you the final result of:

3
1
1/4
1/2

If anyone has a way to shorten this, feel free to contribute.

How about selecting just the root comments first, ordering them and then selecting the child comments and ordering them and finally concatenating both sets? Will that work?