Sorting by custom field and fetching whole tree from DB
- by Niaxon
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?
