0

I'm creating a branching dialog game, and used a dialog tool that outputs JSON with a link and a link_path to connect dialogs together. I've parsed and inserted this structure in PostgreSQL.

I want to query a subset of rows, let's say starting with row 1, and follow the link_path until the link_path is null. Successive rows may be out of order.

For example, in the table below,

  • starting with row 1, I find row with link_path = b,
  • this gives me row 3, I find row with link_path = c,
  • this gives me row 4, row 4's link_path is null, so we return this set: [row 1, row 3, row 4]

--

link     link_path    info
--------------------------
a          b          asdjh
w          y          akhaq
b          c          uiqwd
c                     isado
y          z          qwiuu
z                     nzabo

In PostgreSQL, how can I select rows like this without creating a loop of queries? My goal is performance.

GMB
  • 216,147
  • 25
  • 84
  • 135
user3871
  • 12,432
  • 33
  • 128
  • 268

1 Answers1

1

You can use a recursive query:

with recursive cte as (
    select t.* from mytable t where link = 'a'
    union all
    select t.*
    from cte c
    inner join mytable t on t.link = c.link_path
)
select * from cte

Demo on DB Fiddle:

link | link_path | info 
:--- | :-------- | :----
a    | b         | asdjh
b    | c         | uiqwd
c    | null      | isado
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Is the recursive nature of this query more performant than individual selects row by row? – user3871 Sep 30 '20 at 23:13
  • 1
    @Growler: this will be more efficient than running multiple queries. For your data structure, a recursive query seems like the relevant approach. But basically, it is hard to query such structure really efficiently. There are other ways to represent hierarchical data that you might want to look at. See this SO post for example: https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – GMB Sep 30 '20 at 23:35