CTE using subquery over direct select statement

[alexf-bond]

Following the example here I was able to get a cte working with the output being Select * from a physical table which generates the following as expected:

with an_alias AS
(
  SELECT fizz
  FROM  efg
)
SELECT *
FROM abc
JOIN an_alias on an_alias.fizz = abc.buzz

When attempting the same for a subquery, the final select statement becomes a nested subquery rather than direct access to the subquery with a Select ...

        sub_query_1 = Query.from_(self.table_efg).select("fizz")
        sub_query_2 = Query.from_(self.table_hij).select("buzz")
        test_query = (
            Query.with_(sub_query_1, "an_alias")
            .from_(subquery_2)
            .join(AliasedQuery("an_alias"))
            .on(AliasedQuery("an_alias").fizz == subquery_2.buzz)
            .select(subquery_2.buzz)
        )

Output:

with an_alias AS
(
  SELECT fizz
  FROM  efg
)
SELECT *
FROM (
               SELECT buzz 
               FROM abc
) sq0 JOIN an_alias on an_alias.fizz = sq0.buzz

Is it possible currently to unnest the final select like below?

with an_alias AS
(
  SELECT fizz
  FROM  efg
)
SELECT buzz 
FROM abc JOIN an_alias on an_alias.fizz = sq0.buzz

It's causing a large increase in the execution plan to have to run the subquery then join to it after.

[wd60622]

Check out this example in the tutorial it, it is very similar: https://pypika.readthedocs.io/en/latest/2_tutorial.html#with-clause

Does this fit your use case?

from pypika import Table, AliasedQuery, Query

efg = Table('efg')

sub_query = (
    Query
    .from_(efg)
    .select('fizz')
)

abc = Table("abc")
alias = AliasedQuery("an_alias")
test_query = (
    Query
    .with_(sub_query, "an_alias")
    .from_(abc)
    .join(alias)
    .on(alias.fizz == abc.buzz)
    .select('buzz')
)

WITH an_alias AS (
    SELECT "fizz" FROM "efg"
) 
SELECT "abc"."buzz" 
FROM "abc" 
JOIN an_alias 
ON "an_alias"."fizz"="abc"."buzz"