Support recursive CTEs

[arikkfir]

Support using the WITH RECURSIVE ... keywords for recursive CTEs.

[arikkfir]

I'm working on a PR for this.

[huandu]

Cool. I'll review the PR later. Thank you.

[arikkfir]

Btw @huandu why does CTE automatically add itself to the list of tables when added to a SELECT builder? There are cases where you don't want the CTE in the list of FROM tables implicitly - e.g. when fetching from other tables and doing LEFT JOIN on the CTE.

Perhaps it is better not to implicitly add the CTE name to the list of tables, instead letting the user add them if they want by adding them in the .From(...) call instead?

If you're ok with that notion, I'm happy to create another PR for that (or add it to this one).

[huandu]

why does CTE automatically add itself to the list of tables when added to a SELECT builder? There are cases where you don't want the CTE in the list of FROM tables implicitly - e.g. when fetching from other tables and doing LEFT JOIN on the CTE.

@arikkfir Regarding this design, my initial thought was to make the CTE SQL statement written in Go code WITH ... SELECT more readable. For example, it can now be simply written as With(table).Select("c").Where(...), which is consistent with the way SQL is written. I previously assumed that CTE table names were generally only used in the FROM clause, so I chose this design at that time. I did overlook other possibilities, such as using CTE table names in LEFT JOIN as you mentioned, so I think you've raised a good question, and I need to consider how to handle this design detail better.

[arikkfir]

why does CTE automatically add itself to the list of tables when added to a SELECT builder? There are cases where you don't want the CTE in the list of FROM tables implicitly - e.g. when fetching from other tables and doing LEFT JOIN on the CTE.

@arikkfir Regarding this design, my initial thought was to make the CTE SQL statement written in Go code WITH ... SELECT more readable. For example, it can now be simply written as With(table).Select("c").Where(...), which is consistent with the way SQL is written. I previously assumed that CTE table names were generally only used in the FROM clause, so I chose this design at that time. I did overlook other possibilities, such as using CTE table names in LEFT JOIN as you mentioned, so I think you've raised a good question, and I need to consider how to handle this design detail better.

Sure thing, I appreciate the current design helping simple queries remain concise. Perhaps there can be another CTETableBuilder implementation - e.g. a CTECustomTableBuilder that does not get automatically added, thus maintaining backwards compatibility as well as retaining the syntax sugar for simple CTEs.

Another option is to break backwards compatibility by adding a parameter to the With methods.

Perhaps we should move this discussion to a separate repository Discussion page instead of inside this ticket, which is more focused on supporting recursive CTEs only.