How to write postgres query for below table?

Question

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.

This would be so much easier, if you stored a parentId instead (that way, you could even use a self-referencing foreign key). — pozs
– pozs, Commented Apr 26, 2017 at 7:47

Laurenz Albe · Accepted Answer · 2017-04-26 12:25:39Z

1

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)

answered Apr 26, 2017 at 12:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

user1298426 Over a year ago

You have explicitly mentioned WHERE use AND id = 1; so if 9 is true and 7 is false then it doesn't return anything. Also it doesn't accept any input and run on complete table.

Laurenz Albe Over a year ago

I don't understand that comment, but as you have accepted the other answer, I guess your problem is solved.

pozs · Accepted Answer · 2017-04-26 08:40:06Z

0

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

answered Apr 26, 2017 at 8:40

pozs

36.6k5 gold badges61 silver badges66 bronze badges

1 Comment

user1298426 Over a year ago

Great. I will test it and let you know.

Collectives™ on Stack Overflow

How to write postgres query for below table?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related