Nested set model: retrieve sub-tree containing nodes that match condition I'm using the "nested set model" (the backend used by our web application is CakePHP and the Project model uses the TreeBehavior).

This is my "project" table:

+----+-------+-----------+-----+------+
| id | name  | parent_id | lft | rght |
+----+-------+-----------+-----+------+
|  1 | P1    | NULL      |   1 |    6 |
|  2 | P2    | NULL      |   7 |   12 |
|  3 | P2.1  | 2         |   8 |    9 |
|  4 | P2.2  | 2         |  10 |   11 |
|  5 | P1.1  | 1         |   2 |    3 |
|  6 | P1.2x | 1         |   4 |    5 |
|  7 | P3x   | NULL      |  13 |   18 |
|  8 | P3.1  | 7         |  14 |   15 |
|  9 | P3.2  | 7         |  16 |   17 |
+----+-------+-----------+-----+------+


I need to write a query that will return all projects which name match a certain string and also their parent or children.

So if the string used is "x" ( project.name like '%x%') then the query should return P3.x, P3.1, P3.2, P1.2x and P1.

You can assume that there won't be more than one level but if you have a solution that works for unlimited levels that is even better :)

I have a query that works but it is probably also the least efficient and does not take advantages of the fact that I'm using the nested set model.

SELECT
  *
FROM sr_projects
WHERE id IN (SELECT
  id
FROM sr_projects
WHERE parent_id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE id IN (SELECT
  parent_id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%');


If cannot use stored procedure because CakePHP does not support them.
The nested set model makes this kind of queries, for unlimited levels, easy to write:

SELECT *
FROM sr_projects
WHERE name LIKE '%x%'

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN 
    ( SELECT lft
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON p.lft &lt; x.lft AND x.lft &lt; p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN 
    ( SELECT lft, rght
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON x.lft &lt; ch.lft AND ch.lft &lt; x.rght ;


Test at SQLfiddle-1



It might be more efficient if you use a temp table, because the subquery with the unsargable condition (name LIKE '%x%') will only be run once. But that depends on the version of MySQL you are using. Some newer versions are capable of subquery optimizations (and can actually cache and reuse the subquery results and even automatically index them):

CREATE TEMPORARY TABLE temp_x AS
SELECT *
FROM sr_projects
WHERE name LIKE '%x%' ;


and then:

SELECT *
FROM temp_x

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN 
    temp_x AS x
    ON p.lft &lt; x.lft AND x.lft &lt; p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN 
    temp_x AS x
    ON x.lft &lt; ch.lft AND ch.lft &lt; x.rght ;

DROP TABLE temp_x ;


Test at SQLfiddle-2