具有两个表的邻接列表模型
所以我认为我的问题归结为两个问题:
当树使用邻接列表模型方法存储在 MySQL 中(两个表之间)时,如何在 PHP 中构建可遍历的树结构,而考虑性能?
什么是一种可维护的方法,可以以所需的格式显示树,而不需要重复遍历代码,也不用 if/else 和 switch 语句乱七八糟的逻辑?
以下是更多详细信息:
我正在使用 Zend Framework。
我正在处理一份调查问卷。它存储在 MySQL 数据库中两个单独的表之间:questions 和 Question_groups。每个表都扩展了适当的 Zend_Db_Table_* 类。使用邻接列表模型方法来表示层次结构。
我意识到我遇到的问题可能是由于我将树结构填充到 RDBMS 中,所以我对替代方案持开放态度。但是,我还存储了调查问卷受访者及其回答,因此需要其他方法来支持这一点。
调查问卷需要以各种 HTML 格式显示:
- 作为输入响应的表单(使用 Zend_Form)
- 作为带有问题(和一些组)的有序列表(嵌套)作为按问题或查看响应的链接按组。
- 作为一个有序列表(嵌套),每个问题都附加了答案。
问题是叶节点,question_groups 可以包含其他question_groups 和/或问题。总共有 100 多行需要处理和显示。
目前,我有一个视图助手,它使用递归来检索 Question_group 的子级(在两个表之间执行 UNION 的查询:QuestionGroup::getChildren($id))来完成所有处理。另外,当显示带有问题答复的调查问卷时,需要额外的两个查询来检索受访者及其对每个问题的答复。
虽然页面加载时间不是很长,但这种方法感觉不对。递归加上几乎每个节点的多次数据库查询并没有让我内心感到很温暖和模糊。
目前,以上面列出的格式显示树的逻辑相当混乱。我不想在各处重复遍历逻辑。然而,到处的条件语句也不会产生最容易维护的代码。我已经阅读了 Visitors、Decorator 和一些 PHP SPL 迭代器,但我仍然不清楚它们如何与扩展 Zend_Db_Table、Zend_Db_Table_Rowset 和 Zend_Db_Table_Row 的类一起工作。特别是因为我还没有解决之前从数据库构建层次结构的问题。如果能够轻松地添加新的显示格式(或修改现有的显示格式),那就太好了。
So I think my issue boils down to two questions:
How do I build a traversable tree structure in PHP when the tree is stored in MySQL (between two tables) using the Adjacency List Model approach while keeping performance in mind?
What's a maintainable approach to displaying the tree in the needed formats without duplicating traversal code and littering the logic with if/else and switch statements?
Below are more details:
I'm using the Zend Framework.
I'm working with a questionnaire. It's stored in a MySQL database between two separate tables: questions and question_groups. Each table extends the appropriate Zend_Db_Table_* classes. The hierarchy is represented using the Adjacency List Model approach.
I realize the problems I'm running into are likely due to the fact that I'm stuffing a tree structure into an RDBMS so I'm open to alternatives. However, I'm also storing questionnaire respondents and their responses so alternative approaches would need to support that.
The questionnaire needs to be displayed in various HTML formats:
- As a form for entering responses (using Zend_Form)
- As an ordered list (nested) with questions (and some groups) as links to view responses by question or by group.
- As an ordered list (nested) with responses appended to each question.
Questions are leaf nodes and question_groups can contain other question_groups and/or questions. Combined, there are a little over 100 rows to process and display.
Currently, I have a view helper that does all the processing using recursion to retrieve a question_group's children (a query which performs a UNION between the two tables: QuestionGroup::getChildren($id)). Plus when displaying questionnaire with the question response an additional two queries are needed to retrieve the respondent and their response to each question.
While the page load time isn't very long this approach feels wrong. Recursion plus multiple database queries for almost every node does not make me feel very warm and fuzzy inside.
I've tried recursion-less and recursive methods on the full tree array returned from the UNION to build a hierarchical array to traverse and display. However, that seems to break down since there are duplicated node ids due to the fact that groups and questions are stored in separate tables. Maybe I'm missing something there...
Currently, the logic to display the tree in the formats listed above is quite a mess. I'd prefer not to duplicate the traversal logic all over the place. However, conditionals all over the place don't produce the most easily maintainable code either. I've read up on Visitors, Decorators and some of the PHP SPL iterators but I'm still feeling unclear as to how that would all work together with the classes that are extending Zend_Db_Table, Zend_Db_Table_Rowset and Zend_Db_Table_Row. Especially since I haven't solved the previous problem of building the hierarchy from the database. It would be nice to add new display formats (or modify existing ones) somewhat easily.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
传统上,邻接列表在每行中提供一个
parent_id
列,将行链接到其直接父级。如果该行是树的根,则parent_id
为 NULL。但这会导致您运行许多 SQL 查询,成本高昂。添加另一列
root_id
,以便每一行都知道它属于哪棵树。这样您就可以使用单个 SQL 查询获取给定树的所有节点。向Table
类添加一个方法,以通过树的根 ID 获取Rowset
。编写一个扩展
Zend_Db_Table_Row
的自定义类,并编写函数来检索给定行的父行及其子行的Rowset
。 Row 类应包含受保护的数据对象来引用父级和子级数组。Row
对象还可以具有getLevel()
函数和用于面包屑的getAncestorsRowset()
函数。编写一个扩展
Zend_Db_Table_Rowset
的自定义类,该类具有迭代行集中的行的函数,设置父级和子级引用,以便您随后可以将它们作为树进行遍历。此外,Rowset
应该具有getRootRow()
函数。现在,您可以在行集上调用 getRootRow(),它会返回根节点。获得根节点后,您可以调用 getChildren() 并循环遍历它们。然后,您还可以对这些中间子级中的任何一个调用 getChildren() ,并以您想要的任何格式递归输出树。
The Adjacency List traditionally gives you a
parent_id
column in each row that links a row to its immediate parent. Theparent_id
is NULL if the row is the root of a tree. But this leads you to run many SQL queries, which is expensive.Add another column
root_id
so each row knows what tree it belongs to. That way you can fetch all nodes of a given tree with a single SQL query. Add a method to yourTable
class to fetch aRowset
by the tree's root id.Write a custom class extending
Zend_Db_Table_Row
and write functions to retrieve the given row's parent and also aRowset
of its children. TheRow
class should contain protected data objects to reference the parent and the array of children. ARow
object can also have agetLevel()
function and agetAncestorsRowset()
function for breadcrumbs.Write a custom class extending
Zend_Db_Table_Rowset
that has a function to iterate over the rows in the rowset, setting parent and children references so that you can subsequently traverse them as a tree. Also theRowset
should have agetRootRow()
function.Now you can call
getRootRow()
on a rowset, and it returns the root node. Once you have the root node, you can callgetChildren()
and loop over them. Then you can callgetChildren()
also on any of these intermediate children, and recursively output a tree in any format you want.