Please rebase on top of https://reviews.lunr.nl/r/542/

Too much duplicated code

This is still not incremental. It still shows everything as new. Please make sure you reference your parent properly

While this certainly works, it shoves all the responsibility to create the right query somewhere else, either to the developer or to the escaper. And I can't really think of a escaper function name that wouldn't be confusing or hard to identify. recursive_with_query is a mouthful...
We can do better ;)
Let's see if you can come up with a better solution than this on your own ;)

Still needs a description

Descriptions should start with a capital letter

doesn't it contain the whole WITH query?

Do CTEs only work for SELECT queries?

Same as before

there should probably be a space between the alias and the collumns

same

This is really clumsy for various reasons:
1) can't use recursive CTEs without specifying column_names. If you reverse the order you can't specify column_names without declaring a recursive part. Ideally you want neither.
2) Parameter names are misleading. $recursive is part of the SQL query, so what is the extra $sql_query about?

what about UNION ALL?

This is a good step in the right direction, but you didn't have to throw away all the other code :/
$sql_query alone is too lazy. We can offer an interface for the developer that better covers the WITH RECURSIVE use case.

anchor_query

Sorry, that's wishful thinking.
Individual query parts are meant to be able to be constructed out-of-order. So conceptually, I can define a union upfront, and construct from/where/having/etc later on based on other conditions.
But with this code, I could no longer do that, since the upfront defined union would conflict with a recursive CTE and the query constructed would not at all be what I want it to look like.

When reading this, nobody has any clue wtf this is about. Why would I ever not want all results?

with_recursive()

with_recursive()

query constructing should happen in sql_with()
Don't make the larger use-case fit the smaller interface.

This is confusing, you'd think union = true would mean that it's using UNION

consistent uppercasing for types please

$this or $self?

Missing tests

why make this uppercase?

strict comparisons

Please don't use uppercase for classes.

I would not bind this to the alias. Either bind it to the keyword (WITH) or make it a separate variable

recursive statements only work in the else clause?

I don't think that's correct though. If you check the grammar, the "RECURSIVE" keyword is only mentioned once in the beginning. You can't have that one more than once.

What I read is that it needs to be present as soon as one of the later mentioned CTEs is recursive.

However, since we can specify CTEs in any order in the code, we don't know with the first statement whether there's a recursive one coming later. That's a fun one to solve :)

what if $temp already includes a recursive CTE?
It really helps adding unit tests for the cases that you want to implement. Think about all the cases that could happen, add tests for them, and then make them work.

what about testNonRecursiveWithAfterRecursiveQuery*?

This doesn't work, as the semantic meaning of what's written here means "put WITH statements between WHERE and GROUP BY".
I forgot that SELECT isn't part of the implode, so you'll likely have to handle the "WITH (RECURSIVE)" part directly here (in get_select_query()).

'recursive' is not enough as a variable name. Missing context

You can't write into $this->with. get_*() methods must be without side-effects (calling it twice should yield the same result)

I would use NULL as default value everywhere, but still handle empty strings as well

You don't need a temporary variable.

what if UNION is not set?

Much better :)
Now merge this if with the if below and we are done ;)

Ship It!