MySQL如何在虚拟文件夹中搜索媒体 - MySQL how can I search for media in virtual folders

我正在想办法为我的媒体数据库做一个搜索引擎。

虚拟文件夹的结构为:

表目录

id name parent

媒体的结构是:

表媒体

id name parent

我使用下面的php函数列出我所有的目录

function print_menu($id = 0) {
    $sql = "SELECT directories.id AS id, directories.name AS name, directories.parent AS parent, directories.icon AS icon FROM directories 
    WHERE directories.parent = '".$id."' GROUP BY directories.id ORDER BY directories.name ASC";
    $req = $cnx->prepare($sql);
    $req->execute();
    if ($req->rowCount()>0) {
        echo "<ul>";
        while ($data=$req->fetch()) {
            echo "<li>".$data['name']."</li>";
            print_menu($data['id']);
        }
        echo "</ul>";
    }
 }

现在假设我有以下文件夹

根
- 项目1
  - dir 1
    - 子目录2
  - dir 2
  - dir 3
    - 子目录2
- 项目2
  - dir 1
    - 子目录2
  - dir 2
    - 子目录2
  - dir 3
- 项目3
  - dir 1
  - dir 2
  - dir 3

我想在"项目1"中搜索名为"%media_1%"的媒体，包括它的所有子文件夹，我怎么能在不查询每个文件夹和子文件夹的情况下做到这一点?子文件夹的级别可以变化

一般来说，对于一个"树"表，要获得所有子类的列表和已知的最大深度，您可以构造这样的查询:

SELECT layerN.ID
FROM theTable AS layer1
   INNER JOIN theTable AS layer2 
      ON layer1.id = layer2.parent_id 
      OR layer1.id = layer2.id
   INNER JOIN theTable AS layer3
      ON layer2.id = layer3.parent_id 
      OR layer2.id = layer3.id
   ...
   INNER JOIN theTable AS layerN
      ON `layerN-1`.id = layerN.parent_id 
      OR `layerN-1`.id = layerN.id
WHERE layer1.parent_id = [chosen_parent_node_id]
;

如果在ON条件中没有附加的"OR"子句，则不会包含中间节点。如果不需要中间节点，并且"叶子"节点的深度不同，则INNER JOIN s需要是LEFT JOIN s，并且SELECT字段将稍微复杂一些:

SELECT IFNULL(layerN.id, IFNULL(`layerN-1`.id, IFNULL(....))) AS leafID

或者

您可以有一个额外的表来总结总祖先，最简单的版本将有两个字段ancestor_id和descendant_id(尽管您也可以包括一个"距离")。最大的问题是它需要通过某种形式的代码来维护，树表上的触发器可能是最有效和最可靠的方法。

好的，我找到了一个解决方案，我使用上面发布的函数来获取给定目录的所有子目录的ID，假设"project 1"的ID为42我将使用

$all_ids = array();
function print_menu($id = 0) {
    $sql = "SELECT directories.id AS id, directories.name AS name, directories.parent AS parent, directories.icon AS icon FROM directories 
    WHERE directories.parent = '".$id."' GROUP BY directories.id ORDER BY directories.name ASC";
    $req = $cnx->prepare($sql);
    $req->execute();
    if ($req->rowCount()>0) {
        while ($data=$req->fetch()) {
            $all_ids[] = $data['id'];
            print_menu($data['id']);
        }
    }
 }
all_ids[] = 42;
print_menu(42)

然后我只是做一个请求，比如

SELECT * FROM medias WHERE medias.parent IN (".implode(',', $all_ids).") AND medias.name LIKE '%media_1%'

请求将只搜索"project 1"及其子目录中的媒体，无论有多少个级别