检查n深度树中的值? [英] checking value in n-depth tree?
问题描述
我有两个实体，post 和 category，它们是 1:n 关系.
I have two entities, post and category which is a 1:n relationship.
我有一个包含两列的参考表，post_id,category_id
I have a reference table with two columns, post_id,category_id
categories 表有一个 id 列、一个 status 列和一个 parent_id 列
The categories table has an id column, a status column and a parent_id column
如果一个类别是另一个类别(n-depth)的子类别，那么它的 parent_id 不为空.
If a category is a child of another category (n-depth) then it's parent_id is not null.
如果类别在线，则其状态为 1，否则为 0.
If a category is online it's status is 1, otherwise it is 0.
我需要做的是找出帖子是否可见.
What I need to do is find out if a post is visible.
这需要:
加入帖子的 Foreach 类别跟踪它的树到根节点(直到类别具有 parent_id == null)，如果这些类别中的任何一个具有 status 0 那么该路径被认为是离线的.
Foreach category joined to the post trace up it's tree to the root node (till a category has parent_id == null), if any of those categories have status 0 then that path is considered offline.
如果任何路径在线，则该帖子被认为是可见的，否则它是隐藏的.
If any path is online then the post is considered visible, otherwise it is hidden.
我能想到的唯一方法(作为半伪代码)是:
The only way I can think of doing this (as semi-pseudo code) is:
function visible(category_ids){
categories = //select * from categories where id in(category_ids)
online = false
foreach(categories as category){
if(category.status == 0)
continue;
children = //select id from categories where parent_id = category.id
if(children)
online = visible(children)
}
return online
}
categories = //select c.id from categories c join posts_categories pc on pc.category_id = c.id where pc.post_id = post.id
post.online = visible(categories)
但这可能会导致大量的 sql 查询，有没有更好的方法?
But that could end up being a lot of sql queries, is there a better way?
推荐答案
如果嵌套集不是一个选项，我知道以下几点:
If nested sets are not an option, I know about the following:
- 如果对数据进行排序，以便父级的子级始终跟随在其父级之后，则可以通过跳过输出中的隐藏节点，对所有数据进行一次数据库查询来解决此问题.
这同样适用于排序的嵌套集，原理已在 this answer 中概述，但是关于获取深度不起作用，我建议使用 递归迭代器删除隐藏的项目.
This works equally with a sorted nested set, too, the principle has been outlined in this answer however the algorithms about getting the depth do not work and I would suggest a recursive iterator that is able to remove hidden items.
此外，如果数据未排序，您可以根据 中概述的所有行的(未排序)查询创建树结构嵌套数组的答案.第三层正在消失.不需要递归，你会得到一个可以轻松输出的结构，我也应该在另一个答案中为 <ul>/<li> html 样式输出进行介绍.
Also if the data is not ordered, you can create a tree structure from the (unsorted) query of all rows like outlined in the answer to Nested array. Third level is disappearing. No recursion needed and you get a structure you can easily output then, I should have covered that for <ul>/<li> html style output in another answer, too.
这篇关于检查n深度树中的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
