Implement Recursive CTE for Hierarchical Query to MariaDB

Question

I have this table which I would like to store a chain of records.

CREATE TABLE table_name (
    id INT,
    unique_id varchar,
    reference_id varchar,
);

I want to implement SQL query for MariDB which prints all records by unique_id with all record reference_id. Something like this:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |
| 99 | 454       | 33           |   |   |

I would like when I select record 55544 to get all transactions because each other are using id which points to them. How I can implement this using Recursive CTE? Is there a better way?

Expected result for record with unique_id 55544:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |

How this query can be implemented also in HQL? I want to use it in JPA?

I suppose that complete answer is impossible because of: `How this query can be implemented also in HQL?`. Please correct me if I wrong but HQL does not support all types CTEs and has no way to implement recursive queries. Maybe some 'clever' tricks with NamedQuery/NamedNativeQuery - IDK. — Alex Yu, Feb 02 '19 at 16:21
Can this query be rewritten in JPA? https://stackoverflow.com/a/54394019/1103606 — Peter Penzov, Feb 02 '19 at 17:24
You can run native SQL through JPA. Regarding JPQL - I suppose not. — Alex Yu, Feb 02 '19 at 18:07
You should have the same data type for `reference_id` and `id` not you have type mismatch (INT vs VARCHAR) — Lukasz Szozda, Feb 03 '19 at 13:25

Gordon Linoff · Answer 1 · 2019-02-05T14:09:19.030

8

You can use a recursive CTE. That is the right way to do this:

with recursive cte as (
      select t.*
      from table_name t
      where t.unique_id = 55544
      union all
      select t.*
      from cte join
           table_name t
           on cte.id = t.reference_id
     )
select *
from cte;

edited Feb 05 '19 at 14:09

answered Jan 28 '19 at 00:01

Gordon Linoff

1,242,037
58
646
786

How I can get all records up and down with reference id? Currently I get only below records: https://www.db-fiddle.com/f/sMQvdieGxZA3RB548qPS68/0 – Peter Penzov Feb 05 '19 at 14:26
@PeterPenzov change `on cte.id = t.reference_id` to `on cte.reference_id = t.id` – gonatee Jul 06 '19 at 11:42
This is an absolutely beautiful and super useful piece of SQL. Thank you so much, Gordon! – HartleySan Sep 14 '20 at 12:00

score 2 · Accepted Answer · answered Jan 27 '19 at 23:52

The following query should do what you expect. It's a recursive query that relies on a variable to follow the parent-child relationships trough the dependency tree.

select @ref:=id as id, unique_id, reference_id
from mytable
join (select @ref:=id from mytable WHERE unique_id = 55544)tmp
where reference_id=@ref

This demo on DB Fiddle yields :

| id  | unique_id | reference_id |
| --- | --------- | ------------ |
| 45  | 45454     | 43           |
| 66  | 55655     | 45           |
| 78  | 88877     | 66           |

PS : please note that this does not return the uppermost parent row. If you need it as well, you can change the WHERE condition to :

where reference_id=@ref or unique_id = 55544

I need to ask addition question. How I can get all rows when I send id 66? — Peter Penzov, Feb 05 '19 at 10:50
Thank you, this seems to work even on MySQL 5. I'm wondering though if there's a way to reverse it, and get the IDs up from 78 to 66, 45 and 43 — ᴍᴇʜᴏᴠ, Aug 21 '19 at 05:39

Implement Recursive CTE for Hierarchical Query to MariaDB

2 Answers2

Linked