Sorting by custom field and fetching whole tree from DB

Question

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?

Answer 1

I would approach this with a secondary script that updates a cache of some sort with the data you need. Having a recursive query set for each load could mean trouble (maybe little, maybe lots, depending on usage and other factors).

If this is all shown at once, I would personally run a query that creates the array and updates memcached with it. Depending on my needs, I'd have it run every 15 minutes (if data changed frequently) or hour or two (if it did not). I'd run this from another machine (because I'm blessed, I have a couple of servers, one being a back-end machine for work like this), if one is available.

If memcache isn't available, or if you are loading it only one level at a time, I'd at least add a field to store the size and recursively update those folders with the size. That way you'd only have to pull the level of the node you are viewing, and the size data would be right there, no need to query down any more levels.