4

Using other questions here & Postgresql documentation I've managed to build a many-to-many self joined table.

However adding a WHERE clause is giving me trouble.

Problem:

A Category can have many child categories, and many parent categories. Given a category.Id, I want to retrieve the category, the category children, childrens children and so on.

Example: given this structure:

child_1
    child_11
        child_111
        child_112
            child_1121
    child_21
child_2

Given: a clause of id = child_11

Expected results: child_11, child_111, child_112, child_1121,

Actual results: child_11, child_111, child_112

Here is my attempt: http://sqlfiddle.com/#!17/3640f/2

In case Sqlfiddle is down: https://www.db-fiddle.com/#&togetherjs=LhDjxfPHo6

Note: I don't care about duplicating the where clause, my application can handle that

Table structure:

CREATE TABLE Category(id SERIAL PRIMARY KEY, name VARCHAR(255));
CREATE TABLE Categories(parent_id INTEGER, child_id INTEGER, PRIMARY KEY(parent_id, child_id));

ALTER TABLE Categories ADD FOREIGN KEY (parent_id) REFERENCES category (id);
ALTER TABLE Categories ADD FOREIGN KEY (child_id) REFERENCES category (id);

Table data:

INSERT INTO Category(id, name) VALUES (1, 'parent_1');
INSERT INTO Category(id, name) VALUES (2, 'child_1');
INSERT INTO Category(id, name) VALUES (3, 'child_2');
INSERT INTO Category(id, name) VALUES (4, 'child_3');
INSERT INTO Category(id, name) VALUES (5, 'child_1_1');
INSERT INTO Category(id, name) VALUES (6, 'child_1_2');
INSERT INTO Category(id, name) VALUES (7, 'child_1_1_1');
INSERT INTO Category(id, name) VALUES (10, 'child_of_many');
INSERT INTO Category(id, name) VALUES (11, 'parent_1');
INSERT INTO Category(id, name) VALUES (12, 'parent_2');

INSERT INTO Categories(parent_id, child_id) VALUES (1, 2);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 3);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 4);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 5);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 6);
INSERT INTO Categories(parent_id, child_id) VALUES (5, 7);

My query which is giving me the children, but not the childrens children etc. If I remove the WHERE clause's I can get all rows:

WITH RECURSIVE categories_category AS (
  SELECT id, 'Category' AS COLUMN_TYPE, c1.name 
  FROM Category c1
  WHERE c1.id=2
UNION
  SELECT c2.id, 'Category' AS COLUMN_TYPE, c2.name
   FROM Category c1
   INNER JOIN categories cs1 ON c1.id = cs1.parent_id
   INNER JOIN Category c2 ON c2.id = cs1.child_id
   WHERE cs1.parent_id = 2
) SELECT * FROM categories_category

Edit: More Detailed example:

Given the following category row's, I'd like to be able to run a query given a WHERE clause the matches the id of warmStoreAlcohol and get a result of:

+---+------------------+
|id |name              |
+---+------------------+
|2  |warmStoredAlcohol |
|3  |vodka             |
|4  |beer              |
|5  |frozenBeer        |
+---+------------------+

coldStoredAlcohol would give a result of:

+---+------------------+
|id |name              |
+---+------------------+
|6  |coldStoredAlcohol |
|5  |frozenBeer        |
|7  |cooler            |
+---+------------------+

The database structure will not change often. In this example 'frozenBeer' has two parents, and should return for querying both warmStoredAlcohol and coldStoredAlcohol.

I am open to changing the table structure, adding new tables and even upgrading postgres version etc. The database will hold ~2,000 rows, therefore I value an easy-to-understand table structure over a super complicated optimal one. (But any solution is better than my broken one) enter image description here

  • Instead of running a single recursive (expensive) query on a regular parent/child table, you may be interested in first creating a closure table, which once populated, can give you the answer instantly (no recursion) like 'select DecendentId from TheClosure where AncectorId = 123'. Try searching google "sql closure table" – crokusek Feb 21 at 22:23
3

Recursive CTEs are notoriously hard to learn. Take a look at this fiddle to play with a working model.

The 2nd query in the CTE, or Common Table Expression, needs to reference the first query. This is where the "recursive" name comes from in CTE.

My fiddle has a single table, where each cat can only have a single parent, which is arguably a more common tree scenario. This makes it slightly easier to understand, in my opinion.

Anyway, the DDL consists of:

CREATE TABLE cats
(
    cat_id SERIAL
      PRIMARY KEY
  , cat_name VARCHAR(20) NOT NULL
  , parent_cat_id int NULL
);

ALTER TABLE cats ADD FOREIGN KEY (parent_cat_id) REFERENCES cats (cat_id);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('garfield', NULL);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('bertina', 1);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('oletha', 2);

And the recursive CTE looks like:

;WITH RECURSIVE cats_hierarchy AS
(
  SELECT c1.cat_id
      , c1.cat_name
      , cast('' as varchar(20)) as parent_cat_name
      , c1.parent_cat_id
      , 1 as level
  FROM cats c1
  WHERE c1.parent_cat_id IS NULL

  UNION ALL

  SELECT c2.cat_id
      , c2.cat_name
      , c1.cat_name AS parent_cat_name
      , c2.parent_cat_id
      , c1.level + 1 AS level
  FROM cats c2
      INNER JOIN cats_hierarchy c1 ON c2.parent_cat_id = c1.cat_id
)
SELECT *
FROM cats_hierarchy;

Results look like:

╔═════════╦═══════════╦══════════════════╦════════════════╦═══════╗
║ cat_id  ║ cat_name  ║ parent_cat_name  ║ parent_cat_id  ║ level ║
╠═════════╬═══════════╬══════════════════╬════════════════╬═══════╣
║      1  ║ garfield  ║                  ║ (null)         ║     1 ║
║      2  ║ bertina   ║ garfield         ║ 1              ║     2 ║
║      3  ║ oletha    ║ bertina          ║ 2              ║     3 ║
╚═════════╩═══════════╩══════════════════╩════════════════╩═══════╝

In the CTE the first query (before the UNION ALL) shows cats that are at the root. The 2nd part of the CTE (after the UNION ALL) gets cats related to the parent cat. Those returned results are then queried again by the CTE, generating a tree.

Let me know if that helps, or if you need further clarification.

| improve this answer | |
  • If I added another cat with a parent id of 3, how could I get cats 2, 3, 4 given cat 2? Meaning I want cat 2 and all children, grandchildren etc of cat 2 – dustytrash Feb 21 at 20:25
  • could you add the desired query output to your question? i.e. in table format like in my example... see ozh.github.io/ascii-tables for an easy way to format results. – Max Vernon Feb 21 at 20:31
  • I've started working on a sql fiddle that will allow mutliple parents, but unfortunately I no longer have time to work on it. Here is that fiddle – Max Vernon Feb 21 at 20:45
  • 1
    I edited my question, added expected results as a formatted table. I appreciate your time! I'll edit your answer if I get what you've started working – dustytrash Feb 21 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.