MySQL Recursion

How to use recursion in MYSQL View

You may use a recursive CTE inside a view:

CREATE VIEW categories_view AS
WITH RECURSIVE cte AS (
    SELECT id, parentId, name, name AS path
    FROM categories
    WHERE parentId IS NULL
    UNION ALL
    SELECT c.id, c.parentId, c.name, CONCAT_WS(' > ', t.path, c.name)
    FROM categories c
    INNER JOIN cte t ON c.parentId = t.id
)

SELECT *
FROM cte
ORDER BY id;

Here is a demo showing that the logic is working.

Mysql recursion?

It is possible, but you need to change your database structure; once the changes are made, you can retrieve a tree of any depth in one query. The queries are slightly more complex, but it's still pretty straightforward.

• Storing Hierarchical Data in a Database (SitePoint) - this article is step by step, very clear.
• Managing Hierarchical Data in MySQL - not as clear as the above.

MySql Recursive - get all children and parents from a given id

I would use two separate recursive queries: one to bring the children, the other for the parents, and then union the results. You can keep track of the level of each node to properly order the records int he resultset:

with recursive 
    children as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select c.lvl, d.* from department d inner join children c on c.id = d.father
    ),
    parents as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select p.lvl - 1, d.* from department d inner join parents p on d.id = p.father
    )
select * from parents
union   -- on purpose, to remove the duplicate on id 6
select * from children
order by lvl;

This is safer than having multiple union all members in the same query. MySQL does not guarantee the order of evaluation of the members in the recursion, so using this technique could lead to unexpected behavior.

Demo on DB Fiddle

---

Unrelated to your question, but: the following can be seen in your code:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';

Just don't. ONLY_FULL_GROUP_BY is there for a good reason, that is to have MySQL behave consistenly with the SQL standard as regard to aggregation query. Disabling this SQL mode is never a good idea.

MYSQL Recursive query with conditions

This query gets you the price hirarchically:

with recursive cte(id_product, rate, price, origin_rate) as
(
  select id_product, rate, price, rate
  from mytable
  union all
  select cte.id_product, cte.rate, t.price, t.rate
  from cte
  join map on map.origin_rate = cte.origin_rate
  left join mytable t on t.id_product = cte.id_product
                     and t.rate = map.destination_rate
  where cte.price is null
)
select id_product, rate, price
from cte
where price is not null
order by id_product, rate;

MySQL - Recursively list all parents and ancestors of all items in table

If you are running MySQL 8.0, this is best solved with a recursive query:

with recursive cte as (
    select id, parent_id, 1 lvl from mytable
    union all
    select c.id, t.parent_id, lvl + 1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select id, group_concat(parent_id order by lvl) all_parents
from cte
group by id

Demo on DB Fiddle:

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0     

MySQL CTE Recursive With Join Another Table

You can use this recursive CTE query, which finds all the parent account_type values for a given id_account and then uses GROUP_CONCAT to join the values together to get the account_parent_all value:

WITH RECURSIVE CTE AS (
  SELECT a.id_account, a.account_name, a.account_type, m.account_type_parent, 0 AS depth
  FROM account a
  JOIN account_type_master m ON m.account_type = a.account_type
  UNION ALL
  SELECT c.id_account, c.account_name, c.account_type, m.account_type_parent, c.depth + 1
  FROM CTE c
  JOIN account_type_master m ON m.account_type = c.account_type_parent
  WHERE c.account_type_parent IS NOT NULL
)
SELECT c.id_account, c.account_name, c.account_type, 
       GROUP_CONCAT(c.account_type_parent ORDER BY c.depth SEPARATOR ' => ') AS account_parent_all
FROM CTE c
GROUP BY c.id_account, c.account_name, c.account_type
ORDER BY c.account_name

Output:

id_account  account_name    account_type    account_parent_all
chiira      Chiira          1110            1100 => 1000
rdp         Chloe           1100            1000
lotus       Lotus           1111            1110 => 1100 => 1000

Demo on dbfiddle

---

Related Topics