Sorting items in MPTT result set?

Question

I'm using the MPTT (modified preorder tree traversal) model to store hierarchical data in my MySQL table. (MPTT model: another description is nested set model.). My question is this: has anyone figured out a clever way to sort the results of a query on the tree? I could just go 'ORDER BY label', but then the result set will be sorted by label without respect to the nodes' place or depth in the tree.

Here's an example of the query I use to retrieve the tree of an arbitrary node, with each node's depth:

SELECT node.id, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth
FROM person AS node,
person AS parent,
person AS sub_parent, (
    SELECT node.id, (COUNT(parent.id) - 1) AS depth
    FROM person AS node,
    person AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.id = 1 // that's my root object
    GROUP BY node.id, node.lft
    ORDER BY node.lft
) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.id = sub_tree.id 
GROUP BY node.id
ORDER BY node.lft

As per http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.

Of course, this always orders items by their place in the nested set. What if I wanted to order them by, say, 'lastName' or 'accountBalance' or 'town' instead? Can that be done in MySQL, or do you folks think I'd have to resort to sorting the results in my scripting language?

Answer 1

IMHO having a MPTT tree I always sort by lft (left to right). That is what the tree is all about. If you sort it by any other field, then it's not really hierarchical.