need is WITH statement?

[MigAru]

I can add WITH statement for this lib, you think this need?

[MigAru]

What do you think?

[huandu]

It's a bit hard to define right APIs for WITH statement due to the syntax complexities. Could you share your design thoughts? If you have a design, we can discuss it.

[johndiego]

@MigAru look.. Working with select delete update too!

        keys:= {"tste1","teste2"}
        values:= {"tste1","teste2"}

	ib := sqlbuilder.PostgreSQL.NewInsertBuilder().SQL("with data as (")
	ib.InsertInto("my_table")
	ib.Cols(keys...)
	ib.Values(values...)
	ib.SQL(`returning * ) select TO_JSON(data) as data from data`)

[iambudi]

It's a bit hard to define right APIs for WITH statement due to the syntax complexities. Could you share your design thoughts? If you have a design, we can discuss it.

CTE (Common Table Expression) query in simple way is a wrapper around a SELECT builder using WITH AS syntax so the API might be like this.

sb := sqlbuilder.NewSelectBuilder()

cte1 := sqlbuilder.NewCTEBuilder("cte1", 
    sqlbuilder.Select("column1", "column2").From("table1").Where(sb.Equal("column3", 1))
)

cte2 := sqlbuilder.NewCTEBuilder("cte2", 
    sqlbuilder.Select("column1", "column2").From("cte1").Where(sb.Equal("column1", 2))
)

query := sb.With(cte1, cte2).
    Select("c2.column1", "c2.column2").
    From("cte2 c2").
    Where(sb.Equal("c2.column2", 3))

sql, args := query.Build()
fmt.Println(sql)

Assume using mysql 8.0.x, the output would be

WITH cte1 AS (
    SELECT column1, column2 
    FROM table1 
    WHERE column3 = ?
),
cte2 AS (
    SELECT column1, column2 
    FROM cte1 
    WHERE column1 = ?
)
SELECT 
    c2.column1, 
    c2.column2 
FROM cte2 c2 
WHERE c2.column2 = ?

Before 8, it would be

SELECT 
    c2.column1, 
    c2.column2 
FROM (
    SELECT column1, column2 
    FROM (
        SELECT column1, column2 
        FROM table1 
        WHERE column3 = ?
    ) AS cte1 
    WHERE column1 = ?
) AS c2 
WHERE c2.column2 = ?

[huandu]

@iambudi Thanks for your input. Your design looks pretty clean and readable. I like the idea. I plan to implement it in this week and invite you to review it. Stay tuned.

[huandu]

@iambudi I submit a PR #157 to fix this issue. Do you have some time to comment on the PR for the design? Samples can be found in cte_test.go. Thanks.