CODEWITHSUNDEEP
sqlpostgresql
Bonnie Scott
Bonnie Scott

Reputation: 373

Array field in postgres, need to do self-join with results

I have a table that looks like this:

stuff
    id integer
    content text
    score double
    children[] (an array of id's from this same table)

I'd like to run a query that selects all the children for a given id, and then right away gets the full row for all these children, sorted by score.

Any suggestions on the best way to do this? I've looked into WITH RECURSIVE but I'm not sure that's workable. Tried posting at postgresql SE with no luck.

Upvotes: 1

Views: 739

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658727

The ANY construct in the join condition would be simplest:

SELECT c.*
FROM   stuff p
JOIN   stuff c ON id = ANY (p.children)
WHERE  p.id = 14
ORDER  BY c.score;

Doesn't matter for the query whether the array of children IDs is in the same table or different one. You just need table aliases here to be unambiguous.

Related:

Upvotes: 1

Senthil
Senthil

Reputation: 2246

Similar solution:

With Postgres you can use a recursive common table expression:

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

Ref: Postgresql query for getting n-level parent-child relation stored in a single table

Upvotes: 0

redneb
redneb

Reputation: 23910

The following query will find all rows corresponding to the children of the object with id 14:

SELECT *
FROM unnest((SELECT children FROM stuff WHERE id=14)) t(id)
     JOIN stuff USING (id)
ORDER BY score;

This works by finding the children of 14 as array first, then we convert it into a table using the unnest function, and then we join with stuff to find all rows with the given ids.

Upvotes: 3

Related Questions