Question: Does this support recursive CTE?
Closed this issue · 4 comments
Hi,
Came across this library but couldn't find an example with recursive cte.
For example:
id | parent_id | name |
---|---|---|
1 | NULL | Ikea |
2 | 1 | Light |
3 | 2 | Tradfri |
with recursive category_tree (id, name, parent_id) as (
select id,
name,
parent_id
from category
where id = 3
union all
select c.id,
c.name,
c.parent_id
from category c
inner join category_tree on category_tree.parent_id = c.id
)
select *
from category_tree;
That gives this output:
id | name | parent_id |
---|---|---|
3 | Tradfri | 2 |
2 | Lights | 1 |
1 | Ikea | null |
@RobinHoutevelts Hello! Somehow your issue completely slipped through.
Right now: no, there is no direct explicit support for recursive with - largely because I was not aware of it! However: apparently SQL server does not use the recursive keyword, so self referencing with a UNION should work (in theory).
I will need to look at the Postgres and SQlite syntax to see if there is anything special or it's generic (the library doesn't do platform abstraction).
@RobinHoutevelts just in case you are still interested (I know you posted this a while ago), I've pushed up an initial attempt to add recursive CTEs to the builder. The implementation is a little clunky owing to DBAL not supporting UNIONs in the builder, so I split out the initial select vs query, and added support for defining fields on the basic expression class.
I added tests for building the queries using the code you shared above as one of the examples and I can get that output (after a little massaging to appease DBAL - it doesn't like joins without aliases).
If you want to test it out, use dev-master
as the version. In theory it should work... but please let me know if you experience any isses.
@RobinHoutevelts I am closing your issue. If you do decide to test out the develop, I'd like to know if it worked for your use case with recursive CTEs.
I'm sorry. The project I worked on is finished and I haven't had the chance to try this out again.
However I'm really glad you took upon this feature request! I'm sure I'll get the chance to try it out soon enough! I'll be sure to let you know if I encounter any problems!