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.