使用SQLite创建列表树 [英] Creating a list tree with SQLite

问题描述

我正在尝试使用PHP和这样的SQLite表设置创建层次结构列表:

I'm trying to make a hierarchical list with PHP and an SQLite table setup like this:

    |   itemid  |   parentid    |   name    |
    -----------------------------------------
    |   1       |   null        |   Item1   |
    |   2       |   null        |   Item2   |
    |   3       |   1           |   Item3   |
    |   4       |   1           |   Item4   |
    |   5       |   2           |   Item5   |
    |   6       |   5           |   Item6   |

列表将以无序列表构建，并允许这种类型的树结构:

The lists would be built with unordered lists and allow for this type of tree structure:

Item1
    |_Item3
    |_Item4
Item2
    |_Item5
        |_Item6

我已经看到使用目录和平面数组完成此操作，但是我似乎无法使其在没有深度限制的情况下在这种结构下正常工作.

I've seen this done with directories and flat arrays, but I can't seem to make it work right with this structure and without a depth limit.

推荐答案

您正在使用教科书设计在SQL数据库中存储层次结构数据.这种设计称为邻接表，即层次结构中的每个节点都有一个指向其直接父级的parentid外键.

You're using a textbook design for storing hierarchical data in an SQL database. This design is called Adjacency List, i.e. each node in the hierarchy has a parentid foreign key to its immediate parent.

使用这种设计，您无法生成像描述和支持任意深度的树.您已经知道了.

With this design, you can't generate a tree like you describe and support arbitrary depth for the tree. You've already figured this out.

大多数其他SQL数据库(PostgreSQL，Microsoft，Oracle，IBM DB2)都支持递归查询，从而解决了此问题.但是SQLite和MySQL尚不支持SQL的此功能.

Most other SQL databases (PostgreSQL, Microsoft, Oracle, IBM DB2) support recursive queries, which solve this problem. But SQLite and MySQL don't support this feature of SQL yet.

因此，您需要另一种解决方案来存储层次结构.有几种解决方案.有关说明和示例，请参见我的演示文稿使用PHP和MySQL的分层数据模型

So you need another solution to store the hierarchy. There are several solutions for this. See my presentation Models for Hierarchical Data with PHP and MySQL for descriptions and examples.

我通常更喜欢一个称为闭合表"的设计，但是每个设计都有优点和缺点.哪种查询最适合您的项目，取决于您需要有效处理数据的哪种查询.因此，您应该去研究解决方案，然后自己选择一种.

I usually prefer a design I call Closure Table, but each design has strength and weaknesses. Which one is best for your project depends on what kinds of queries you need to do efficiently with your data. So you should go study the solutions and choose one for yourself.

这篇关于使用SQLite创建列表树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！