Find X parents with recursive postgres

Question

Have issue with building a recursive query in Postgres.

Tried with this one but it doesn't seem to work. So table user have user_id and referred_by_user_id. referred_by_user_id can be empty, if it is empty we don't need to go up anymore.

So need to find all referrers above a user, going in dynamical depth. Current implementation with limit is naive, I would assume I can exit as soon as I reach X depth.

WITH RECURSIVE uplines(user_id, referred_by_user_id) AS (
 SELECT t.user_id, t.referred_by_user_id
 FROM postgres.user AS t
 WHERE t.user_id = $userId
 UNION ALL
 SELECT t.user_id, t.referred_by_user_id
 FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
)
SELECT uplines.user_id FROM uplines LIMIT $depth;

Have read bunch of SO posts about it but still cannot get it to work.

• ypercubeᵀᴹ: It would help if you provided the table definition and sample data. You can use dbfiddle.uk and provide the link in the question.

Answer 1

I think what you want can be achieved by adding in the column list a "depth" or "level" that increases by one for each recursion:

WITH RECURSIVE uplines(user_id, referred_by_user_id, depth) AS (
 SELECT t.user_id, t.referred_by_user_id, 0
 FROM postgres.user AS t
 WHERE t.user_id = $userId
 UNION ALL
 SELECT t.user_id, t.referred_by_user_id, a.depth + 1
 FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
 WHERE a.depth < $depth
)
SELECT user_id, depth
FROM uplines 
ORDER BY depth ;