Как правильнее сделать выборку и построения графа рефераллов?

Question

[Vladislav]

Есть таблица Users(ее упрощенный вид выглядет примерно так):
id | referrer_id
id - ид юзера
referrer_id - кто его пригласил

нужно построить граф(фактически это будет дерево), где корень это получается у нас некоторый пользователь(например, текущий, id = 1)
тогда получается мы сначала находим всех его "подопечных"
SELECT * from USERS where referrer_id = 1
Допустим мы получили несколько юзеров, которые будут "под" ним, теперь получается, чтобы отрисовать следующий уровень(и так для всех уровней) нужно опять для каждого из этой линии делать подобный запрос что ли? или есть способ получше?

И еще вопрос какую лучше библиотеку использовать на JS, дабы отображать это?

Answer 1

[Марат]

MySQL обязательно использовать? Нет возможности сменить его на Postgres (или другие СУБД где есть рекурсивные запросы - Oracle/MSSQL/DB2)?

Есть или нет готовые библиотеки для отрисовки из СУБД не знаю. Но возможно получить из таблицы пути для построения дерева. Если устроит то следующим образом.
(К сожалению под рукой нет postgresql,) запрос примерно такой:

WITH 
    RECURSIVE search_graph(id, ref_id) AS
    (
        SELECT id, ref_id
        FROM Users
        UNION ALL
        SELECT o.id, o.ref_id
        FROM Users o
    JOIN search_graph p ON p.ref_id = o.id
    )
    SELECT array_agg(id || ' -> ' ) AS "path"
    FROM search_graph
    GROUP BY id
    ORDER BY id
;

Должен выдать:
1 ->
2 -> 1 ->
3 -> 1 ->
4 -> 1 ->
5 -> 4 -> 1 ->

Последний '->' можно при необходимости убрать через substring(str, from 1 for (length(str)-4))

P.S. С MySQL форками и последними версиями не работал - может и появились рекурсивные запросы - не знаю. Или возможно через хранимую процедуру/функцию сделать аналог.