"recursive" query in postgresql

Question

this is my current query :

SELECT
    id, name,
    CASE 
        when geolocation='' then (select geolocation from location where id = q1.parent_id)
        else geolocation end
FROM location q1;

this is an example of the table on which i make my query and the current result i get:

id | name | geolocation | parent_id 
-- | ---- | ----------- | ---------
 1 | aaaa |   0.0,0.0   |    
 2 | bbbb |   1.1,1.1   |     1
 3 | cccc |             |     1
 4 | dddd |   2.2,2.2   |  
 5 | eeee |             |
 6 | ffff |             |     3

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff |

this is not the result i want to get. what i would like is the request to be "recursive" so that the 6th location gets the geolocation of the "root parent" (in this case location aaaa). so my expected result is :

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff |   0.0,0.0

i tried to use the "cte method" but i can't get the result i want...

anyone has an idea?

best regards and have a great day

ps : careful, geolocation is a string

If you need to support a hierarchical structure of data - this might help stackoverflow.com/a/38701519/5962802 — IVO GELOV
– IVO GELOV, Commented Jul 29, 2021 at 9:05

wildplasser · Accepted Answer · 2021-07-29 11:55:20Z

1

\i tmp.sql

CREATE TABLE wtf
        ( id integer PRIMARY KEY
        , name text
        , geolocation text
        , parent_id integer REFERENCES wtf(id)
        );

INSERT INTO wtf
        ( id , name , geolocation , parent_id ) VALUES
  (1 ,'aaaa' ,'0.0,0.0',NULL)
 , (2 ,'bbbb' ,'1.1,1.1',1)
 , (3 ,'cccc' ,'',1)
 , (4 ,'dddd' ,'2.2,2.2', NULL )
 , (5 ,'eeee' ,'',NULL)
 , (6 ,'ffff' ,'',3 )
        ;

SELECT * FROM wtf;

        -- find the "root" (if any) for all entries
WITH RECURSIVE opa AS (
        SELECT id AS opa
        , id AS moi
        FROM wtf
        WHERE parent_id IS NULL -- The roots
    UNION
        SELECT o.opa AS opa -- inherit the root name from your parent
                ,m.id AS moi
        FROM wtf m -- the descendants
        JOIN opa o ON (m.parent_id = o.moi)
        )
SELECT w.*
        , x.name AS opa_name
        , x.geolocation AS opa_geolocation
FROM wtf w
JOIN opa o ON (w.id = o.moi)
LEFT JOIN wtf x ON (x.id = o.opa)
        ;

Results:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 6
 id | name | geolocation | parent_id 
----+------+-------------+-----------
  1 | aaaa | 0.0,0.0     |          
  2 | bbbb | 1.1,1.1     |         1
  3 | cccc |             |         1
  4 | dddd | 2.2,2.2     |          
  5 | eeee |             |          
  6 | ffff |             |         3
(6 rows)

 id | name | geolocation | parent_id | opa_name | opa_geolocation 
----+------+-------------+-----------+----------+-----------------
  1 | aaaa | 0.0,0.0     |           | aaaa     | 0.0,0.0
  4 | dddd | 2.2,2.2     |           | dddd     | 2.2,2.2
  5 | eeee |             |           | eeee     | 
  2 | bbbb | 1.1,1.1     |         1 | aaaa     | 0.0,0.0
  3 | cccc |             |         1 | aaaa     | 0.0,0.0
  6 | ffff |             |         3 | aaaa     | 0.0,0.0
(6 rows)

answered Jul 29, 2021 at 11:55

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Armand Violle Over a year ago

thank you for your answer @wildplasser, works perfectly fine. just added this query in the end to get the final result : ``` SELECT id, name, CASE when geolocation = '' then opa_geolocation else geolocation end as geolocation FROM this_intermediate ORDER BY id asc; ``` thank you again and have a great day, best regards.

wildplasser Over a year ago

Yes, you could use COALESCE() to massage the final results. (cleaner than the CASE expression) NOTE: opa is Dutch for grandpere ;-)

Mitko Keckaroski · Accepted Answer · 2021-07-29 12:11:28Z

0

The first idea is to use a function.

CREATE OR REPLACE  FUNCTION get_last_location(int) RETURNS text
    AS $$ 

       WITH RECURSIVE subordinates AS 
        (
         SELECT
         id,parent_id, name,geolocation from location 
         WHERE
         id =$1

         UNION

         SELECT
           e.id,e.parent_id, e.name,e.geolocation from location e
          inner JOIN subordinates s ON s.parent_id = e.id
        )
    select geolocation from subordinates 
    where not (geolocation is null) order by id limit 1
    
    $$
    LANGUAGE SQL;

Then use this select:

SELECT
    id, name,
    CASE 
        when geolocation is null then (select get_last_location(q1.id))
        else geolocation end
FROM location q1;

The query without using function

SELECT
    id, name,
    CASE 
        when geolocation is null then 
        (
               WITH RECURSIVE subordinates AS 
                  (
                   SELECT
                   id,parent_id, name,geolocation from location 
                   WHERE
                   id =q1.id

                   UNION

                   SELECT
                     e.id,e.parent_id, e.name,e.geolocation from location e
                    inner JOIN subordinates s ON s.parent_id = e.id
                  )
                  select geolocation from subordinates 
                  where not (geolocation is null) order by id limit 1
        
        )
        else geolocation end
FROM location q1;

The Output

edited Jul 29, 2021 at 12:11

answered Jul 29, 2021 at 9:22

Mitko Keckaroski

1,0891 gold badge10 silver badges16 bronze badges

9 Comments

Armand Violle Over a year ago

hi @MitkoKeckaroski, thank you for your answer, but it only works with id 1 or am I wrong ? i would like something "generic" that does it for each parent...

Mitko Keckaroski Over a year ago

just add the id of any element in the WERE condition

Armand Violle Over a year ago

ok i see, the problem is that my table is dynamic so that would not work...

Armand Violle Over a year ago

no idea on how to do it without specifying the "root" id ?

user330315 Over a year ago

@ArmandViolle: typically the "root id" is defined by using where parent_id is null

|

Andrei Odegov · Accepted Answer · 2021-07-29 12:22:47Z

0

Try the following query:

  r AS (
      SELECT id, name, geolocation
      FROM location
      WHERE parent_id IS NULL
    UNION ALL
      SELECT
        l.id,
        l.name,
        COALESCE(l.geolocation,
                 r.geolocation)
      FROM r
      JOIN location AS l
        ON r.id = l.parent_id
  )
SELECT * FROM r;

Result:

+----+------+-------------+
| id | name | geolocation |
+----+------+-------------+
|  1 | aaaa | 0.0,0.0     |
|  4 | dddd | 2.2,2.2     |
|  5 | eeee |             |
|  2 | bbbb | 1.1,1.1     |
|  3 | cccc | 0.0,0.0     |
|  6 | ffff | 0.0,0.0     |
+----+------+-------------+

db<>fiddle

edited Jul 29, 2021 at 12:22

answered Jul 29, 2021 at 12:15

Andrei Odegov

3,5722 gold badges18 silver badges26 bronze badges

1 Comment

Armand Violle Over a year ago

Ogedov I tried the exact same thing before asking the question thinking it would work too, but it doesn't... thanks for your answer

Collectives™ on Stack Overflow

"recursive" query in postgresql

3 Answers 3

2 Comments

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related