2

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.

  • It would help if you provided the table definition and sample data. You can use dbfiddle.uk and provide the link in the question. – ypercubeᵀᴹ Feb 24 '18 at 12:44
3

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.