CODEWITHSUNDEEP
sqlpostgresqlhierarchical-datarecursive-query
user3541631
user3541631

Reputation: 4008

Using recursion with a self Foreign key

I'm using PostgreSQL 10, and I have the following structure:

A table Type with a foreign key to itself.

    id  name   parent_id
    1  namea    Null
    2  nameb    Null
    3  namea1   1
    4  namea11  3 
    5  namea111 4 
    6  nameb1   2 
    7  nameb2   2

A table Item_Type for a Many to Many relation

   id type_id item_id
   1    1      1
   2    3      2
   3    5      3
   4    7      4

Table Item which has M2M relation to Type.

 id   name 
  1  item1   
  2  item2   
  3  item3   
  4  item4

At this moment, I'm using an additional path field, which I calculate every time I make operations(crud) with Type.

I'm wondering if is not faster and easy to try to use the PostgreSQL recursion.

I checked the documentation but I didn't understand very well, because I get an error, and I don't understate why.

WITH RECURSIVE descendants AS (
 SELECT id, name FROM Type WHERE id = 1
 UNION
 SELECT t.id, t.name, t.parent_id FROM Type AS t
 INNER JOIN descendants AS d ON d.id = t.parent_id
) SELECT * FROM descendants;

ERROR: each UNION query must have the same number of columns

What I need - Giving a Type name:

1) Get all names/id for the requested Type and is descendants

2) Get all Item for the requested Type and is descendants, and the number of Item per Type and descendants

For example:

If the requested Type name is 'namea1', I should get for Type ids 1,3,4,5 and for Item ids 1,2,3

Upvotes: 0

Views: 425

Answers (1)

FXD
FXD

Reputation: 2060

The error says it all. Your union is divided between:

  • SELECT <2 fields> from Type ...
  • SELECT <3 fields> from Type JOIN Descendant ...

Simply select 3 fields on both halves:

WITH RECURSIVE descendants AS (
 SELECT id, name, parent_id FROM Type WHERE id = 1
 UNION
 SELECT t.id, t.name, t.parent_id FROM Type AS t
 INNER JOIN descendants AS d ON d.id = t.parent_id
) SELECT * FROM descendants;

Upvotes: 2

Related Questions