with clause nested query.

[nishantkshyp2004]

Hello, could anyone help me to get this query using pypika.

WITH A as (
		SELECT `id`, `name`, `age` FROM `customer`
	),
 B as (
		SELECT `id`, `name`, `age` FROM `A` WHERE `age` > 18
	  ),
 C as (
		SELECT `id`, `name`, `age` FROM `B` ORDER BY `age`, `name` DESC
	  ),
 D as (
		SELECT `id`, UPPER(`name`) as `name`, `age` FROM `C`
	  ),
 E as (
		SELECT * FROM `D` limit 100 offset 0
	  )
SELECT * FROM E;

I tried many combination using .with_ using couldnt able to generate this use cases.

[wd60622]

Doesn't something like this work?

from pypika import AliasQuery, Field, functions as fn, Query


CUSTOMER = "customer"
A = AliasedQuery("A")
B = AliasedQuery("B")
C = AliasedQuery("C")
D = AliasedQuery("D")
E = AliasedQuery("E")

query_a = Query.from_(CUSTOMER).select("id", "name", "age")
query_b = Query.from_(A).select("id", "name", "age").where(Field("age") > 18)
query_c = Query.from_(B).select("id", "name", "age").orderby("age", "name")
query_d = Query.from_(C).select("id", fn.Upper("name").as_("name"))
query_e = Query.from_(D).select("*").limit(100).offset(0)

query = (
    Query
    .with_(query_a, "A")
    .with_(query_b, "B")
    .with_(query_c, "C")
    .with_(query_d, "D")
    .with_(query_e, "E")
    .from_(E).select("*")
)