StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONested Set Model: Query node childs only 1 level below AND all parents above
text
Body
copied!<p>I am using Nested Set Model with MySQL to create hierarchical tree model.</p> <p>I have successfully managed to get Node and all its Childs 1 level below (I need to get only 1 sub-level)</p> <p>However, I would like to have not only all Childs 1 level below, but all Parents above that Node too.</p> <p>Is it possible to modify current query to get what I want?</p> <p>This is my code (actually it's taken from this, non-working, site: <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/</a> but mine is exactly the same):</p> <pre><code>SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth <= 1 ORDER BY node.lft; </code></pre> <p>Tree structure looks like this:</p> <pre><code>Electronics --Televisions ----LCD --Portable Electronics ----MP3 Players ------Flash ----CD Players </code></pre> <p>Using my current code above, I get this:</p> <pre><code>--Portable Electronics ----MP3 Players </code></pre> <p>But I need to get something like this:</p> <pre><code>Electronics --Portable Electronics ----MP3 Players </code></pre> <p>I will be using this query later in PHP, so I can use PHP-based solution (or part of it) too.</p> <p>Thank you</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload