Query to get all rows that have recursive associations in another table

Not sure how to explain this with words so I will begin with an example.

I have a table called Assets

SELECT * FROM Assets

id	name
1	asset1
2	asset2

Assets can "convert" into another Asset. There is additional data associated with this "conversion". This is represented with the table AssetConversion

asset_id_in	asset_id_out	additional_data
1	2	somedata
2	3	somedata3
3	4	somedata3
5	6	somedata4

From this table, we can see the following conversion of Assets


1 -> 2 -> 3 -> 4

5 -> 6

The question is: Given an Asset ID, how can I get all previous Assets?

Ex: Given Asset ID 4, get all previous Assets in order. So a result like the following

id	name
4	asset4
3	asset3
2	asset2
1	asset1

Solution

You can do this with a recursive query:

WITH RECURSIVE links(id, name) AS (
    SELECT id, name
    FROM assets
    WHERE id = 4     -- Specify starting asset id here
  UNION
    SELECT assets.id, assets.name
    FROM assetconversion ac
    JOIN links ON ac.asset_id_out = links.id
    JOIN assets ON ac.asset_id_in = assets.id
)
SELECT *
FROM links

You start with the row of which you specify the id value. The recursive query then adds prior assets in the order in which the records are found. If no more prior records are found then the recursion stops and the result is made available to the outer query.

The order in the result of the recursive query will be the order in which the records are added to that result so there should be no ORDER BY clause in the outer query. However, if you further qualify this result with other clauses (join to other relations, WHERE clause, etc) then the order is not guaranteed as PostgreSQL may reorder records as it sees fits (as per the SQL standard). If you are certain that the id value in a chain of assets is always increasing as you go along the chain, then you can add an ORDER BY id DESC clause.