How to get child nodes without grandchildren and great-grandchildren? Closure Table pattern

Question

I am using tree storage. Closure Table pattern.

https://towardsdatascience.com/closure-table-pattern-to-model-hierarchies-in-nosql-c1be6a87e05b

How to get child nodes without grandchildren and great-grandchildren?

I have a sql query:

SELECT p.id, p.name, count(*) - l.level AS level 
FROM   tree_Path t, 
       ( SELECT count(*) -1 AS level 
         FROM tree_path 
         WHERE children = 3
       ) l, 
       (  SELECT * 
          FROM category_name f 
               INNER JOIN tree_Path t 
                    ON f.id = t.children 
           WHERE t.parent = 3 
           AND t.children!=t.parent
        ) p 
WHERE p.children = t.children 
GROUP BY p.id, p.name, l.level;

It returns a complete tree of descendants, including grandchildren and great-grandchildren.

    +----+----------------------------+-------+
    | id | name                       | level |
    +----+----------------------------+-------+
    |  7 | Audio & Video Accessories  |     2 |
    |  8 | Camera & Photo Accessories |     2 |
    | 15 | 3D Glasses                 |     3 |
    | 16 | Antennas                   |     3 |
    | 17 | Accessory Bundles          |     3 |
    | 18 | Bags & Cases               |     3 |
    +----+----------------------------+-------+

I need to return my children without grandchildren and great-grandchildren.

    +----+----------------------------+-------+
    | id | name                       | level |
    +----+----------------------------+-------+
    |  7 | Audio & Video Accessories  |     2 |
    |  8 | Camera & Photo Accessories |     2 |
    +----+----------------------------+-------+

How to do it?

DB TEST

-- phpMyAdmin SQL Dump
-- version 5.1.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Aug 02, 2021 at 01:15 PM
-- Server version: 8.0.25
-- PHP Version: 8.0.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `closure_table`
--

-- --------------------------------------------------------

--
-- Table structure for table `category_name`
--

CREATE TABLE `category_name` (
  `id` bigint NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

--
-- Dumping data for table `category_name`
--

INSERT INTO `category_name` (`id`, `name`) VALUES
(1, 'Electronics'),
(2, 'Computers'),
(3, 'Accessories & Supplies'),
(4, 'Camera & Photo'),
(5, 'Computer accessories and peripherals'),
(6, 'Computer components'),
(7, 'Audio & Video Accessories'),
(8, 'Camera & Photo Accessories'),
(9, 'Accessories'),
(10, 'Bags & Cases'),
(11, 'Audio & Video Accessories'),
(12, 'Blank Media'),
(13, 'Computer screws'),
(14, 'Table Barebones'),
(15, '3D Glasses'),
(16, 'Antennas'),
(17, 'Accessory Bundles'),
(18, 'Bags & Cases'),
(19, 'Accessory Kits'),
(20, 'Bags & Cases'),
(21, 'Bag & Case Accessories'),
(22, 'Binocular Cases'),
(23, 'Computer headsets'),
(24, 'Computer microphones'),
(25, 'Discs BD-R'),
(26, 'Discs BD-RE'),
(27, 'bolts'),
(28, 'frame'),
(29, 'lamps'),
(30, 'coasters');

-- --------------------------------------------------------

--
-- Table structure for table `tree_path`
--

CREATE TABLE `tree_path` (
  `children` bigint NOT NULL,
  `parent` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

--
-- Dumping data for table `tree_path`
--

INSERT INTO `tree_path` (`children`, `parent`) VALUES
(1, 1),
(3, 1),
(4, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(15, 1),
(16, 1),
(17, 1),
(18, 1),
(19, 1),
(20, 1),
(21, 1),
(22, 1),
(2, 2),
(5, 2),
(6, 2),
(11, 2),
(12, 2),
(13, 2),
(14, 2),
(23, 2),
(24, 2),
(25, 2),
(26, 2),
(27, 2),
(28, 2),
(29, 2),
(30, 2),
(3, 3),
(7, 3),
(8, 3),
(15, 3),
(16, 3),
(17, 3),
(18, 3),
(4, 4),
(9, 4),
(10, 4),
(19, 4),
(20, 4),
(21, 4),
(22, 4),
(5, 5),
(11, 5),
(12, 5),
(23, 5),
(24, 5),
(25, 5),
(26, 5),
(6, 6),
(13, 6),
(14, 6),
(27, 6),
(28, 6),
(29, 6),
(30, 6),
(7, 7),
(15, 7),
(16, 7),
(8, 8),
(17, 8),
(18, 8),
(9, 9),
(19, 9),
(20, 9),
(10, 10),
(21, 10),
(22, 10),
(11, 11),
(23, 11),
(24, 11),
(12, 12),
(25, 12),
(26, 12),
(13, 13),
(27, 13),
(28, 13),
(14, 14),
(29, 14),
(30, 14),
(15, 15),
(16, 16),
(17, 17),
(18, 18),
(19, 19),
(20, 20),
(21, 21),
(22, 22),
(23, 23),
(24, 24),
(25, 25),
(26, 26),
(27, 27),
(28, 28),
(29, 29),
(30, 30);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `category_name`
--
ALTER TABLE `category_name`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tree_path`
--
ALTER TABLE `tree_path`
  ADD PRIMARY KEY (`children`,`parent`),
  ADD KEY `FK_PARENT` (`parent`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `category_name`
--
ALTER TABLE `category_name`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tree_path`
--
ALTER TABLE `tree_path`
  ADD CONSTRAINT `FK_CHILDREN` FOREIGN KEY (`children`) REFERENCES `category_name` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_PARENT` FOREIGN KEY (`parent`) REFERENCES `category_name` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I need children without grandchildren and great-grandchildren. — Michael Joseph Jackson, Aug 04 '21 at 11:54
I do not see the difference between the relations C->H and C->K... — Akina, Aug 04 '21 at 11:55
I see 2 ways from C to K - direct and via F. And absolutely the same 2 ways from C to H. What is the **difference**? What link is present on the C->K way which is absent in C->H way (or backward)? — Akina, Aug 04 '21 at 12:03
*"K is a great-grandson for C"* - Not being a great grandson is not part of you requirements though? Your requirements are descendants of C without grandchildren and great-grandchildren. K is a descendent of C, and K has no grandchildren or great-grandchildren, so this fulfils the criteria. I think you may need to elaborate on or refine your exact criteria. — GarethD, Aug 04 '21 at 12:05
@GarethD You said correctly. I only need children without grandchildren and great-grandchildren — Michael Joseph Jackson, Aug 04 '21 at 12:12
Knowing the node ID, I want to get all of its children. Isn't that impossible? — Michael Joseph Jackson, Aug 04 '21 at 12:17
H is also a daughter of F! H and K are both exactly the same, they both have the same 3 parents (A, C, F), neither has any grandchildren (or children for that matter). I cannot fathom why K would not be in your results when H is? — GarethD, Aug 04 '21 at 12:21
@GarethD I understood you. I found this drawing on the Internet. And each looks at his own angle. Everyone builds their own chain. So I'll just say - How to get all children without grandchildren and great-grandchildren. Now I will correct the main post. — Michael Joseph Jackson, Aug 04 '21 at 12:33
Also, how does this question differ from your [previous question](https://stackoverflow.com/q/68622567/1048425) with an accepted answer? — GarethD, Aug 04 '21 at 12:38
The question is the same, but the sql answer is not what I need. Because of this, I have difficulties. — Michael Joseph Jackson, Aug 04 '21 at 12:43
If the answer to your previous question is not what you need, you should not have marked it as the accepted answer. You should not re-post the same question. If you don't get more answers to a given question, you may need to wait, or else post a [bounty](https://stackoverflow.com/help/bounty). — Bill Karwin, Aug 04 '21 at 15:14

score 2 · Accepted Answer · answered Aug 04 '21 at 13:01

2

Assuming you want level = 2 results and no greater descendants, use a HAVING clause:

SELECT p.id, p.name, count(*) - l.level AS levelx
FROM   tree_path t, 
       ( SELECT count(*) -1 AS level 
         FROM tree_path 
         WHERE children = 3
       ) l, 
       (  SELECT * 
          FROM category_name f 
               INNER JOIN tree_path t 
                    ON f.id = t.children 
           WHERE t.parent = 3 
           AND t.children!=t.parent
        ) p 
WHERE p.children = t.children 
GROUP BY p.id, p.name, l.level
HAVING levelx = 2
;

See your SQL in a fiddle:

Working test case with adjusted SQL with HAVING clause

answered Aug 04 '21 at 13:01

Jon Armstrong

4,654
2
12
14

What a handy tool. Thanks. – Michael Joseph Jackson Aug 04 '21 at 13:12
Using aggregate-free condition(s) in HAVING clause might be inefficient. Consider moving them to WHERE – Michael Joseph Jackson Aug 04 '21 at 15:54
levelx is from aggregation (and is functionally dependent on the `GROUP BY` terms). `count(*) - l.level AS levelx` ... Both `COUNT(*)` and `l.level` are aggregates or explicitly taken from `GROUP BY` terms. This shouldn't be a problem. – Jon Armstrong Aug 04 '21 at 16:16
Note: Moving that to the `WHERE` clause would break the logic. But we could wrap this in a CTE term or derived table and then filter levelx later. It's just not necessary here. This is why we have `HAVING`. – Jon Armstrong Aug 04 '21 at 16:18
OK. I understood. Thanks. – Michael Joseph Jackson Aug 04 '21 at 16:27

How to get child nodes without grandchildren and great-grandchildren? Closure Table pattern

1 Answers1