Hello everyone,

I am trying to do file browser in a tree form and have a problem to sort it somehow. I use PHP and MySQL for that. I've created mixed (nested set + adjacency) table 'element' with the following fields:

element_id, left_key, right_key, level, parent_id, element_name, element_type (enum: 'folder','file'), element_size.

Let's not discuss right now that it is better to move information about element (name, type, size) into other table.

Function to scan specified directory and fill table work correctly. Noteworthy, i am adding elements to tree in specific order: folders first and then files.

After that i can easily fetch and display whole table on the page using simple query:

SELECT * FROM element WHERE 1=1 ORDER BY left_key

With the result of that query and another function i can generate correct html code (<ul><li>... and so on). to display tree.

Now back to the question (finally, huh?). I am struggling to add sorting functionality. For example i want to order my result by size. Here i need to keep in my mind whole hierarchy of tree and rule: folders first, files later.

I believe i can do that by generating in PHP recursive query:

SELECT * FROM element WHERE parent_id = {$parentId} ORDER BY element_type (so folders would be first), size (or name for example) asc/desc

After that for each result which has type = 'folder' i will send another query to get it's content.

Also it's possible to fetch whole tree by left_key and after that sort it in PHP as array but i guess that would be worse :)

I wonder if there is better and more efficient way to do such a thing?