按自定义字段排序并从数据库中获取整个树
我正在尝试以树形形式进行文件浏览器，但在以某种方式对其进行排序时遇到问题。 我为此使用 PHP 和 MySQL。我已经创建了具有以下字段的混合（嵌套集+邻接）表“元素”：
element_id, left_key, right_key, level, parent_id, element_name, element_type (enum: 'folder','file'), element_size.
现在我们不讨论最好将有关元素（名称、类型、大小）的信息移动到其他表中。
扫描指定目录并填充表的功能正常工作。值得注意的是，我按特定顺序向树添加元素：首先是文件夹，然后是文件。
之后，我可以使用简单的查询轻松获取并在页面上显示整个表格：
SELECT * FROM element WHERE 1=1 ORDER BY left_key
通过该查询的结果和另一个函数，我可以生成正确的 html 代码 (
... 等等).显示树。
现在回到问题（最后，是吧？）。我正在努力添加排序功能。 例如，我想按尺寸对结果进行排序。在这里，我需要记住树和规则的整个层次结构：首先是文件夹，然后是文件。
我相信我可以通过在 PHP 递归查询中生成来做到这一点：
SELECT * FROM element WHERE parent_id = {$parentId} ORDER BY element_type (so folders would be first), size (or name for example) asc/desc
之后，对于每个具有 type = 'folder' 的结果，我将发送另一个查询以获取其内容。
另外，还可以通过 left_key 获取整个树，然后在 PHP 中将其作为数组进行排序，但我想这会更糟:)
我想知道是否有更好、更有效的方法来做这样的事情？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我将使用辅助脚本来解决此问题，该脚本用您需要的数据更新某种缓存。为每个负载设置递归查询可能意味着麻烦（可能很少，也可能很多，具体取决于使用情况和其他因素）。
如果一次全部显示，我将亲自运行一个查询来创建数组并用它更新 memcached。根据我的需要，我会让它每 15 分钟运行一次（如果数据经常更改）或一两个小时运行一次（如果数据不经常更改）。如果有的话，我会从另一台机器上运行它（因为我很幸运，我有几台服务器，其中一台是用于此类工作的后端机器）。
如果内存缓存不可用，或者一次只加载一层，我至少会添加一个字段来存储大小，并用该大小递归更新这些文件夹。这样，您只需拉取正在查看的节点的级别，大小数据就在那里，无需再向下查询任何级别。
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.