MySQL - Nested set model: searching in nodes

Question

Hello.

Let's just say I have tree in my table called "page":

Foo
  -- Bar 1
  -- Bar 2
      -- Bar 3
  -- Some foo
  -- Some value
Bar
  -- Bar 4

I want to search by node's name. For example "bar%". Excpected output should be:

Foo
  -- Bar 1
  -- Bar 2
     -- Bar 3
Bar
  -- Bar 4

I can't find solution to write right query in MySQL. Searching is not a problem - I don't know how to get a depth of a node in search results.

Answer 1

SELECT  mc.*,
        (
        SELECT  COUNT(*)
        FROM    page mp
        WHERE   mc.lft BETWEEN mp.lft AND mp.rgt
        ) AS depth
FROM    page mc
WHERE   mc.name LIKE 'bar%'

This query can be improved if you create your (lft, rgt)'s as a single field sets LineString and make a SPATIAL INDEX over this field:

SELECT  mc.*,
        (
        SELECT  COUNT(*)
        FROM    page mp
        WHERE   MBRWIthin(Point(0, mc.lft), mp.sets)
        ) AS depth
FROM    page mc
WHERE   mc.name LIKE 'bar%'

See this article for more details:

• Adjacency list vs. nested sets: MySQL

Answer 2

See here (includes additional examples)