Design Discussion: How to use CTE table name in JOIN and WHERE clause

[huandu]

In current design, if we call With() method on SelectBuilder, CTE table names will be automatically added to the FROM clause in SELECT statement. This behavior is not expected sometimes if we want to use CTE table in JOIN or WHERE clause, not in FROM. However, there is no way to change this behavior. We need to design new API or do some break changes to fix it.

Based on comments 1 and 2 by @arikkfir in #161, I create this issue to discuss how to solve this issue. Feel free to join this discussion and share your thoughts.

I have two possible solutions. My personal preference is the solution 1.

Solution 1: Keep CTETable() as it is, and add new method CTEQuery() to build CTE query without any side effect

CTETableBuilder will be renamed to CTEQueryBuilder, which is just designed to build CTE query without any side effect, e.g. adding its name in FROM clause. To preserve current behavior, CTETable() will return *CTEQueryBuilder with a flag indicating CTEBuilder to add this CTE query name to FROM clause.

Ideally, this change will not break any existing API, as the struct CTETableBuilder is not expected to be used directly by any user. To ensure backward compatibility, I'll add a type alias type CTETableBuilder = CTEQueryBuilder.

Following is a sample using this new API.

b := With(
    // CTEQuery will NOT be added to FROM clause automatically.
    CTEQuery("f").As(
        Select("id", "a").From("foo"),
    ),

    // CTETable will be added to FROM clause automatically.
    // We can read CTETable as "CTE query to be used as a table".
    CTETable("b").As(
        Select("b").From("bar"),
    ),
).Select("t.id", "f.a", "b.b").From("t").Join(
    "f", "f.id = t.id",
)
println(b.String())

// Output:
// WITH f AS (SELECT id, a FROM foo), b AS (SELECT b FROM bar) SELECT t.id, f.a, b.b FROM t, b JOIN f ON f.id = t.id

My initial design, adding CTE table names to FROM clause automatically, is to improve readability of related Go code.

From the sample code below, we can see that using this design makes the writing of Go code very similar to the writing of SQL itself, which should enhance the readability and maintainability of the code.

go-sqlbuilder/cte_test.go

Lines 13 to 31 in 8cd72ce

	func ExampleWith() {
	sb := With(
	CTETable("users", "id", "name").As(
	Select("id", "name").From("users").Where("name IS NOT NULL"),
	),
	CTETable("devices").As(
	Select("device_id").From("devices"),
	),
	).Select("users.id", "orders.id", "devices.device_id").Join(
	"orders",
	"users.id = orders.user_id",
	"devices.device_id = orders.device_id",
	)
	fmt.Println(sb)
	// Output:
	// WITH users (id, name) AS (SELECT id, name FROM users WHERE name IS NOT NULL), devices AS (SELECT device_id FROM devices) SELECT users.id, orders.id, devices.device_id FROM users, devices JOIN orders ON users.id = orders.user_id AND devices.device_id = orders.device_id
	}

Solution 2: BREAKING CHANGE - Explicitly set table names in SelectBuilder.With() method to add CTE table in FROM clause

Another way to solve this issue is to completely remove the side effect, with which all CTE table names will be automatically added to FROM clause. Instead, adding second param names in SelectBuilder.With(builder *CTEBuilder, names ...string) to allow us to add CTE table names to FROM clause explicitly.

Here is a sample.

cteb := With(
    CTETable("f").As(
        Select("id", "a").From("foo"),
    ),
    CTETable("b").As(
        Select("b").From("bar"),
    ),
)
sb := Select("t.id", "f.a", "b.b").From("t").Join(
    "f", "f.id = t.id",
).With(cteb, "b") // Explicitly add `b` in `FROM` clause
println(sb.String())

// Output:
// WITH f AS (SELECT id, a FROM foo), b AS (SELECT b FROM bar) SELECT t.id, f.a, b.b FROM t, b JOIN f ON f.id = t.id

---

There could be other solutions to solve this issue. Looking forward to any feedback on this topic.

cc @slok @iambudi, because of your previous contribution on CTE.

[arikkfir]

👍 for solution 1 from me too

[iambudi]

I choose solution #1, no breaking changes and "makes the writing of Go code very similar to the writing of SQL itself".

[huandu]

Thanks all for your feedback. I'll implement the solution 1 by this weekend.