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.

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 ;