CODEWITHSUNDEEP
postgresql
user1298426
user1298426

Reputation: 3717

How to write postgres query for below table?

This is very weird query and I am not sure how to proceed with it. Below is the table.

id  descendentId    attr_type   attr_value
1   {4}               type_a    
2   {5}               type_a    
3   {6}               type_a    
4   {7,8}             type_b    
5   {9,10}            type_b    
6   {11,12}           type_b    
7   {}                type_x    TRUE
8   {}                type_y    "ABC"
9   {}                type_x    FALSE
10  {}                type_y    "PQR"
11  {}                type_x    FALSE
12  {}                type_y    "XYZ"

Input for the query will be 1,2,3.. output should be "ABC".

Logic is - loop through descendantId from 1,2,3 until attr_type x is reached. If attr_type x is reached which is 7,9 and 11 then check which one is true. For e.g. 7 is true, then get it's sibling of type type_y (check row 4) which is 8 and return it's value.

All this is string format.

Upvotes: 0

Views: 61

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246163

This is really a complicated data model for such a query, but my way is to flatten out the hierarchy first:

WITH RECURSIVE
   typex(id, use) AS (
         SELECT id, attr_value::boolean
         FROM weird
         WHERE attr_type = 'type_x'
      UNION
         SELECT w.id, typex.use
         FROM weird w
            JOIN typex
               ON ARRAY[typex.id] <@ w.descendentid
   ),
   typey(id, value) AS (
         SELECT id, attr_value
         FROM weird
         WHERE attr_type = 'type_y'
      UNION
         SELECT w.id, typey.value
         FROM weird w
            JOIN typey
               ON ARRAY[typey.id] <@ w.descendentid
   )
SELECT id, value
FROM typex
   NATURAL JOIN typey
WHERE use
  AND id = 1;

┌────┬───────┐
│ id │ value │
├────┼───────┤
│  1 │ ABC   │
└────┴───────┘
(1 row)

Upvotes: 1

pozs
pozs

Reputation: 36214

This can be solved with recursive CTEs:

with recursive params(id) as (
  select e::int
  from   unnest(string_to_array('1,2,3', ',')) e -- input parameter
),
rcte as (
    select '{}'::int[] parents,
           id,
           descendent_id,
           attr_type,
           attr_value
    from   attrs
    join   params using (id)
  union all
    select     parents || rcte.id,
               attrs.id,
               attrs.descendent_id,
               attrs.attr_type,
               attrs.attr_value
    from       rcte
    cross join unnest(descendent_id) d
    join       attrs on attrs.id = d
    where      d <> all (parents) -- stop at loops in hierarchy
)
select y.attr_value
from   rcte x
join   rcte y using (parents) -- join siblings
where  x.attr_type  = 'type_x'
and    x.attr_value = 'true'
and    y.attr_type  = 'type_y'

http://rextester.com/YDLDH11322

Upvotes: 0

Related Questions