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.