What is the best way to make a selection and build a referral graph?

Question

PHP

There is a Users table (its simplified form looks something like this):
id | referrer_id
id - user id
referrer_id - who invited him,
you need to build a graph (in fact, it will be a tree), where the root is some user (for example, current, id = 1)
then it turns out we first find all his "wards"
SELECT * from USERS where referrer_id = 1
Let's say we got several users who will be "under" him, now it turns out that in order to draw the next level (and so for all levels), we need to make a similar request again for each of this line or what? or is there a better way?
And another question is what is the best library to use on JS in order to display this?

Answer 1

MySQL must be used? Is there no way to change it to Postgres (or other DBMS where there are recursive queries - Oracle/MSSQL/DB2)?
Whether or not there are ready-made libraries for rendering from the DBMS, I don’t know. But it is possible to get paths from the table to build a tree. If it does, then proceed as follows.
(Unfortunately, there is no postgresql at hand,) the query is something like this:

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
;

Should output:
1 ->
2 -> 1 ->
3 -> 1 ->
4 -> 1 ->
5 -> 4 -> 1 ->
The last '->' can be removed if necessary via substring(str, from 1 for (length(str)-4))
PS Didn't work with MySQL forks and latest versions - maybe recursive queries appeared - I don't know. Or it is possible to make analog through stored procedure/function.