How to select a recursive structure given some parent ids in mysql?

Question

I am trying to get all folders and consequent subfolders given multiple parent_ids. The database table has the following structure:

   id           name         parent_id
   1           Folder1          null
   2           Folder2          null  
   3           Folder3           1
   4           Folder4           1
   5           Folder5           2
   6           Folder6           2
   7           Folder7           4

The following query works for a single id, however I want to avoid doing multiple queries. For multiple ids for instance 1 and 6, it should return the rows (1, 3, 4, 7 and 6).

SELECT id,
       name,
       parent_id
FROM
  (SELECT *
   FROM folder_categories
   ORDER BY parent_id, id) categories_sorted,
  (SELECT @pv := ?) initialisation
WHERE (FIND_IN_SET(parent_id, @pv)
       AND @pv := CONCAT(@pv, ',', id))
      OR id = ?

Note: Question mark might be any id.

Any idea how can I adapt this query to accept multiples ids?

Edit: My mysql version is 5.7 so unfortunately WITH RECURSIVE is not available.

what output you are expecting and what is the version of your MySQL? — Akhilesh Mishra, Sep 12 '20 at 15:57
Possible duplicate of https://stackoverflow.com/questions/192220/ — Bill Karwin, Sep 12 '20 at 17:57

score 3 · Answer 1 · answered Sep 12 '20 at 16:01

3

For multiple ids for instance 1 and 6, it should return the rows (1, 3, 4, 7 and 6).

If you can use CTE (MySQL8), the following query returns them.

with recursive cte as
( select id, name, parent_id, id as top
   from folder_categories
   where id in (1,6)
  union all select a.id, a.name, a.parent_id, b.top
   from folder_categories a inner join cte b on a.parent_id=b.id)
select * from cte;

See db fiddle

answered Sep 12 '20 at 16:01

etsuhisa

1,698
1
5
7

Unfortunately I am running on mysql 5.7, but that's a good approach with CTE! – Exprove Sep 12 '20 at 17:49

score 0 · Answer 2 · answered Sep 15 '20 at 12:12

If you do not use CTE, you need to determine the search depth.

For example, for a query that searches up to 4 levels:

select distinct x.* from folder_categories x inner join
 (select a.id id1, b.id id2, c.id id3, d.id id4
   from
     folder_categories a
     left outer join
     folder_categories b on a.id=b.parent_id
     left outer join
     folder_categories c on b.id=c.parent_id
     left outer join
     folder_categories d on c.id=d.parent_id
   where a.id in (1,6)) y
  on x.id=y.id1 or x.id=y.id2 or x.id=y.id3 or x.id=y.id4;

However, you may not get enough performance when the table has many rows.

How to select a recursive structure given some parent ids in mysql?

2 Answers2