nevermind nevermind - 27 days ago 15
MySQL Question

How to fetch all siblings using SQL closure tables pattern (without subquery!)

I'm trying to implement the closure table pattern for a simple Menu model, but I've met some difficulties building the query to find all siblings of the current node without subqueries (eg. with joins).

There is an old question very similar to mine but doesn't seems to have an answer (or at least I didn't understand it).

Take for example the following simplified scenario (doesn't include zero depth records):

menu:
+--+--------------+
| id | title |
+--+--------------+
| 1 | Link 1 |
| 2 | Link 1.1 |
| 3 | Link 1.2 |
| 4 | Link 1.3 |
| 5 | Link 1.3.1 |
| 6 | Link 1.3.2 |
+----+------------+

menu_closure:
+----------+------------+-------+
| ancestor | descendant | depth |
+----------+------------+-------+
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 2 |
| 1 | 6 | 2 |
| 4 | 5 | 1 |
| 4 | 6 | 1 |
+----------+------------+-------+


I want to obtain all siblings of the Link 1.1 (id=2) -> Link 1.2 (id=3) and Link 1.3 (id=4).

Note: I know only the id of the targeted
menu
record.


Currently, I do the following:

SELECT m.*
FROM menu AS m
LEFT JOIN menu_closure AS mc ON mc.descendant=m.id
WHERE m.id != 2
AND mc.depth = 1
AND mc.ancestor = (SELECT ancestor FROM menu_closure WHERE descendant=3 AND depth=1)


Another option that I was thinking of was to first get the parent of Link 1.1 and then fetch its children by excluding Link 1.1's id, but I'm searching for a solution with only 1 query.

Answer

You first check for the ancestor

select *
from menu_closure a
where a.descendant = 2

then pick up the siblings

select *
from menu_closure a
join menu_closure s on s.ancestor = a.ancestor
where a.descendant = 2

at the same depth as "Link 1.1"

select *
from menu_closure a
join menu_closure s on s.ancestor = a.ancestor
where a.descendant = 2
    and s.depth = a.depth

add in the menu titles

select *
from menu_closure a
join menu_closure s on s.ancestor = a.ancestor
join menu m on m.id = s.descendant
where a.descendant = 2
    and s.depth = a.depth

and exclude everything not wanted

select m.*
from menu_closure a
join menu_closure s on s.ancestor = a.ancestor
join menu m on m.id = s.descendant
where a.descendant = 2
    and s.depth = a.depth
    and m.id <> 2

Final sqlfiddle