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

Question

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.

I cannot confirm any problems, see http://sqlfiddle.com/#!9/f671c6/1/0 — Olaf Dietsche, Nov 12 '16 at 09:07
If I understand correctly, your query is "give me all the items with the same ancestor and depth as x." Somehow you're gong to have to look up the immediate ancestor to link 1.1 to make that work. Tweaks on the current query: you can use an inner `JOIN` (not LEFT) and should be `... descendant=2 ...` If you really don't want a subquery, you can join menu_closure to itself, as in the example you link to, but the subquery seems much more transparent for the next guy who has to deal with the code. — Jerry, Nov 12 '16 at 09:16
@OlafDietsche The query works fine, but I was searching for a solution with joins (without subquery) — , Nov 12 '16 at 10:11
@Jerry Thanks for the suggestion, I will give it a try. btw. The reason why I don't want to use sub-query is because I think in most of the times using JOIN is faster than sub-queries and especially for MySql (if I'm not wrong) sub-queries are executed for each record separately when they are in the WHERE statement. — , Nov 12 '16 at 10:19
@RyanVincent I'm not a fan of the nested sets pattern. In my case I have a lot of insertion/deletion. — , Nov 12 '16 at 10:42

Olaf Dietsche · Accepted Answer · 2016-11-12T10:42:01.133

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

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

1 Answers1