MySQL hierarchical recursive query for fetch data

Question

Consider this is my table structure....

id | name        | parent_id
19 | category1   | 0
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21
......

Now i want to fetch privious related rows with single mysql query. if i give `id=22' then query should return me id 21,20,19.

Also need to fetch (It can be a separate query) level wise data return. i.e. if i give id=22 then query should return only id 21 (first level). id 21,20 (secound level)...

Here's a similar link! Accept answer almost going to solve my problem but it only work assending order means when parent_id < id.

A portion of accepted answer :

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

What version of MySQL are you using? MySQL 8+ supports recursive CTEs. — Gordon Linoff, Sep 15 '19 at 16:25
See "Alternative 2: Path-style Identifiers" in the answer from your link. — Paul Spiegel, Sep 15 '19 at 16:36

Paul Spiegel · Answer 1 · 2019-09-24T09:23:10.247

1

You can create a stored function which will traverse the tree:

delimiter //
create function product_ancestors(in_id int) returns text reads sql data
begin
  declare current_id int default in_id;
  declare ancestor_ids text default null;

  while (current_id <> 0) do
    set current_id = (
      select parent_id
      from products
      where id = current_id
    );
    if (current_id <> 0)
      then set ancestor_ids = concat_ws(',', ancestor_ids, current_id);
    end if;
  end while;

  return ancestor_ids;
end //

delimiter ;

Use it with

select product_ancestors(22);

That will return a comma delimited string '21,20,19'.

See demo

edited Sep 24 '19 at 09:23

answered Sep 15 '19 at 17:06

Paul Spiegel

30,925
5
44
53

i have no idea where to store this function and how can i run. Can you please give me some hints sir? i use phpmyadmin for mysql GUI. – Mustaque Ahmed Sep 24 '19 at 07:53
@MustaqueAhmed In phpMyAdmin: Choose your DB. Click on "SQL" tab. Paste the first query. Then click "OK". Use the function with `select product_ancestors(22);` – Paul Spiegel Sep 24 '19 at 09:22

score 0 · Answer 2 · answered Sep 15 '19 at 16:52

You can adapt the query from the link in your question by reversing things in it

select  id,
        name,
        parent_id, @pv 
from    (select * from products
         order by id desc) products_sorted,
        (select @pv := '21') initialisation
where   find_in_set(id, @pv)
and     length(@pv := concat(@pv, ',', parent_id))

Demo

score -2 · Answer 3 · answered Sep 15 '19 at 16:30

In MySQL 8+, you can use a recursive CTE:

with recursive cte as (
      select c.id, c.parent_id
      from categories c
      where c.id = 22
      union all
      select cte.id, c.parent_id
      from cte join
           categories c
           on c.id = cte.parent_id
     )
select *
from cte;

Use a join to bring in the names, if you need them as well.

Here is a db<>fiddle.

MySQL hierarchical recursive query for fetch data

3 Answers3