PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – With multiple self reference id fields, how can I query to get info on all of them?

Given the following sql table

| id | name  | mother | father | brothers  |
|----|-------|--------|--------|-----------|
| 1  | danny | 3      | 2      | {4, 6, 7} |
| 2  | bob   | 20     | 30     | {}        |
| 3  | marge | 10     | 50     | {}        |
| 4  | rose  | 3      | 2      | {1, 6, 7} |

I want to get the names of the siblings of danny (id: 1).

I can do it with 2 queries and the help of programming language, making 1 query like

SELECT mother, father, brothers
FROM people
WHERE id = 1

then process it and do

SELECT name
FROM people
where id IN (3, 2, 4, 6, 7)

but I wonder if I could achieve the same result with a single query.

Edit:

In this example there are "just" 3 fields, I will be having around 15 of these fields in the real use scenario.

I found that I can do

SELECT "name"
FROM   people
WHERE  id IN
       (
              SELECT mother AS sibling
              FROM   people
              WHERE  id = 1
              UNION
              SELECT Unnest(brothers) AS sibling
              FROM   people
              WHERE  id = 1)

is that the most effective way? performance wise it doesn’t seem efficient since every union runs the same query again and again. Unless under the hood postgres knows how to optimize it.
Also this query involve lot of repeating of the from and where.

Tags:

2

Answers


  1. 0

    Something like this would probably work:

    SELECT m.name, f.name
FROM people t1
LEFT OUTER JOIN people m ON t1.mother = m.mother and m.name != 'danny'
LEFT OUTER JOIN people f ON f1.father = f.mother and f.name != 'danny'
WHERE t1.name = 'danny'
AND (m.mother IS NOT NULL OR f.father IS NOT NULL)

    You could obviously enhance this to retrieve the columns you want, remove duplicated, etc.

    Login or Signup to reply.
  2. 0 
    SELECT c.id, c.name, s.sibling_names
FROM   people c
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT s.name
      FROM   unnest(c.brothers) id
      JOIN   people s USING (id)
      )
   ) s(siblings) ON true
WHERE  c.id = 1;

    Related:

    If father and mother are always known, you best drop the redundant column brothers (which should be called "siblings"). Then the query could be:

    SELECT c.id, c.name, s.*
FROM   people c
LEFT   JOIN LATERAL (
   SELECT ARRAY(
      SELECT name
      FROM   people s
      WHERE  s.id <> c.id
      AND   (s.mother = c.mother OR s.father = c.father)
      )
   ) s(siblings) ON true
WHERE  c.id = 1;

    In a gender-fluid world, check more thoroughly:

          ...
      AND   (s.mother IN (c.mother, c.father) OR s.father IN (c.father, c.mother))
      ...

    Either query returns people without siblings, too.
    And neither duplicates siblings. (In the first case, the list strictly represents the brothers array.

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search