this is my current query :
x
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
Advertisement
Answer
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)