Selecting a node in the nested set model through a not-unique name

Question

Hello,

I am trying to manage the retrieval of a node in a nested set model table, not through the unique ID, but through the name (a string), and other nodes within the tree under different parents may be called the same way.

As far as now I used an unique ID to get nodes inside the nested sets:

SELECT
     node.name, node.lft, node.rgt
FROM tbl AS parent, tbl AS node
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.id = '{$node_id}'
GROUP BY node.id

Trying to extend this method to a more general way to retrieve the node through its name, I came up with a query containing as much HAVING clauses as the depth of the node to retrieve, checking for the node name and its depth:

SELECT
     node.name, node.lft, node.rgt, COUNT(node.id) AS depth
FROM tbl AS parent, tbl AS node
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
HAVING
     (node.name = 'myParentName' AND depth = 1)
     OR
     (node.name = 'myParent2Name' AND depth = 2)
     OR
     ...
 # and so on

But it is not perfect: having two nodes with the same name and the same depth, but within different parents, both are retrieved, no matter the hierarchy they belong to.

Example:

ARTICLES
   |
   +--PHP
   |   +--the-origins
   |   +--syntax
   +--JS
       +--history
       +--syntax

In this case, the query above would return either ARTICLES/PHP/syntax or ARTICLES/JS/syntax: a "syntax" node with depth 3, infact, is either under the PHP node or under the JS node. Is there an effective path to walk, to solve this problem?

Answer 1

I'm not quite sure what you're trying to do here. Are you trying to access the node with the pathname ARTICLES/PHP/syntax? If so what you'd need to do would be a self-join for each parent level:

SELECT n0.*
FROM tbl AS n0
JOIN tbl AS n1 ON n0.lft BETWEEN n1.lft AND n1.rgt
JOIN tbl AS n2 ON n1.lft BETWEEN n2.lft AND n2.rgt
WHERE n0.name='syntax'
AND n1.name='PHP'
AND n2.name='ARTICLES'