How to add a parent where clause on a Nested set sql query for a menu structure

Question

Im' working with a fully functional nesetd set system in PHP
I'm using it to build a simple menu structure with max depth = 2

Now I've added in the table an additional field to control the visibility state of every single menu item, in other words if it's active or not. My need is that If I deactivate a parent I want that the query doesn't return any of the childrens even if they are active.

This is the actual code for retrieve the whole menu tree

SELECT    `nesty`.*,
          (COUNT(`parent`.`id`) - (`sub_tree`.`depth` + 1)) AS `depth`

FROM      `menus` AS `nesty`,
          `menus` AS `parent`,
          `menus` AS `sub_parent`,
          (
             SELECT `nesty`.`id`,
                    (COUNT(`parent`.`id`) - 1) AS `depth`

             FROM   `menus` AS `nesty`,
                    `menus` AS `parent`

             WHERE  `nesty`.`lft`  BETWEEN `parent`.`lft` AND `parent`.`rgt`
             AND    `nesty`.`id`     = 1
             AND    `nesty`.`tree_id`  = 1
             AND    `parent`.`tree_id` = 1

             GROUP BY `nesty`.`id`

             ORDER BY `nesty`.`lft`
          ) AS `sub_tree`

WHERE    `nesty`.`lft`   BETWEEN `parent`.`lft`     AND `parent`.`rgt`
AND      `nesty`.`lft`   BETWEEN `sub_parent`.`lft` AND `sub_parent`.`rgt`
AND      `sub_parent`.`id` = `sub_tree`.`id`
AND      `nesty`.`tree_id`   = 1
AND      `parent`.`tree_id`  = 1
GROUP BY `nesty`.`id`

HAVING   `depth` > 0

ORDER BY `nesty`.`lft`

So basically I want to add in the where clause

AND      `nesty`.`visible`   = 1

If I add that it's working wel as long the deactivate items are not parent of someone So I need that a menu item isn't returned if has the visible field equals 0 and/or if its parent has the visible field equals 0

thnx in advance

score 0 · Answer 1 · answered Mar 26 '13 at 18:18

0

Well, have you tried adding:

and (nesty.visible <> 0 and parent.visible <> 0)

answered Mar 26 '13 at 18:18

Gordon Linoff

1,242,037
58
646
786

Already tried..this does not return the parent if has visible = 0 but returns children – Fed03 Mar 27 '13 at 15:28

score 0 · Answer 2 · answered Mar 26 '13 at 22:04

Firstly, let me congratulate you on the very readable SQL statement you posted. I wouldn't have spent time with it otherwise.

Unfortunately, I still find the statement hard to understand and I can't avoid thinking that statement should not be so complex. Nested sets are supposed to make recursive queries simple exactly by avoiding referencing the table for each level of the tree, which appears to be what you are doing.

A query to retrieve any subtree of your menus should be as simple as:

select menus.*
from menus
where menus.id between [plft] and [prgt]

where plft and prgt are parameters defined by the left and right values of the root node of the subtree. If your main tree root has id=1, then you can write:

select menus.*
from menus
where menus.id between (select lft from menus where id=1) and 
                       (select rgt from menus where id=1)

I will provide an untested answer based on this typical nested set query.

The first step is to identify the nodes explicitly hidden, the subquery hidden:

select menus.id, lft, rgt
from   menus
where  menus.visible = 0

Second step, obtain all subtrees which are implicitly hidden, the subquery hidden_all:

select menus.id
from
    (select id, lft, rgt
     from   menus
     where  menus.visible = 0) as hidden
inner join menus 
on (menus.lft between hidden.lft and hidden.rgt)

Final step, retrieve all menu options except those in subquery hidden_all. This can be done with a outer join or using the operator IN. Using the last option:

select menus.*
from   menus
where  menus.lft between plft and prgt
       and not menus.id in (
    select    menus.id
    from      (select m.id, m.lft, m.rgt
               from   menus m
               where  m.visible = 0) as hidden
    inner join menus m
    on         (m.lft between hidden.lft and hidden.rgt)
    )

As I mentioned before, it is untested and you need to replace plft and prgt with the correct values or make them parameters.

thank u but as I said the query is provided by the component I'm using and without what I wnt to add is fully operative^ — Fed03, Mar 27 '13 at 15:30
Are you able to explain the query yourself? I find it too time consuming to understand the query without data. Consider using http://sqlfiddle.com/ to put a decent sample of your data there. — koriander, Mar 27 '13 at 20:21
If u are interested [this article](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) is worth reading — Fed03, Mar 28 '13 at 00:47
Indeed, that explains most of it. I suppose the tree_id=1 condition it's because table menus can have more than one hierarchy in it. — koriander, Mar 28 '13 at 10:37

score 0 · Answer 3 · answered Mar 27 '13 at 20:55

I wrote the package you're referring to and that query. It's based on this blog post by Mike Hillyer.

The three implicit joins are used to both gain the "depth" attribute in each result relative to the parent, using the Modified Preorder Tree Traversal algorithm.

What I have typically done is got the tree of nodes from your database, like you're doing. When rendering them, look at the 'visibility' property of each node. I'm assuming you're using it stock-standard, with the CRUD model. So:

// Looping through your nodes if ( ! $node->visibility) continue;

You can do something like that when you're outputting the result from your query.

Nested-Sets 2, the rewrite of Nesty has that query written with the illuminate/database query builder, not just (virtually) plain SQL. We can inject closures and stuff like that to modify the query.

Yeah I absolutly can use that condition in php loop, I was just wondering if it could be possible in sql thinking about optimization XD Glad that the creator of this component shows here, many thanks. I look forward to work with your new components in a next project(hope so) — Fed03, Mar 28 '13 at 00:52
Is there a reason not to include the depth of a node as a field? Having to compute the depth introduces inefficiency and, even worse, makes queries hard to read and as shown with this question, very hard to maintain. Including depth as a field would allow to solve this issue only with SQL, as I've shown below. — koriander, Mar 28 '13 at 10:40

koriander · Answer 4 · 2013-03-28T20:47:22.113

I'm sorry to say this, but the nesty library is really bad technology. It is a less than ideal nested set implementation. Depth should never be computed, a depth field should be part of the data model as indicated in wikipedia http://en.wikipedia.org/wiki/Nested_set_model. Furthermore, the query above should never be used to return the full tree. All that complexity is only required for subtrees.

Please try this new patch:

= AND      `parent`.`tree_id`  = 1
+ and      nesty.lft not in (
         select menus.lft
         from   menus,
                (select     lft, rgt
                 from       menus
                 where      menus.visible = 0
                 and        menus.tree_id = 1
                ) as hidden
         where  menus.lft between hidden.lft and hidden.rgt
         and    menus.tree_id = 1
                          )

And, if table menus includes a depth field, the complete query for the full tree is:

select     nesty.*, 0 as depth0
from       menus as nesty,
           (select lft, rgt from menus where menus.id = 1) as root
where      nesty.lft between root.lft and root.rgt
and        nesty.tree_id  = 1
and        not nesty.lft in (
                select    menus.lft
                from      menus,
                          (select lft, rgt
                           from   menus
                           where  visible = 0
                           and    menus.tree_id = 1
                          ) as hidden
                where     menus.lft between hidden.lft and hidden.rgt
                and       menus.tree_id = 1
                            )

Working but partially if I have 2 distinct parent nodes hidden the query fails — Fed03, Mar 28 '13 at 15:53
of course, silly me!! the correct solution should be based on my original proposal. Do you think you can adapt it? Else, I'll look at it later on. — koriander, Mar 28 '13 at 16:05
I'm heading out of my office right now. I'll look into it tomorrow and keep u informed^ — Fed03, Mar 28 '13 at 17:43
sadly neither the edited version works...I think that I have to stick to the php guard.... — Fed03, Mar 29 '13 at 18:34

How to add a parent where clause on a Nested set sql query for a menu structure

4 Answers4