SQL query to obtain entire tree by root id

Question

I have a table to store binary tree data in the following structure

Lets say for userId 27, currently I'm fetching a bunch of rows and building the tree in code.
Is there an efficient way to fetch the all the rows which belonging to the tree for userId 27 (as nodes) via sql query?
I have MySQL 8.0.21.

Answer 1

In MySQL 8.0, one option uses a recursive query:

with recursive cte as (
    select t.* from mytable where userid = 27
    union all
    select t.*
    from cte c
    inner join mytable t on t.parentid = c.userid
)
select * from cte