Repository: stephenafamo/bob (go)

Issue:
Chain From Mod
Following PSQL documentation: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-FROM
`The FROM clause derives a table from one or more other tables given in a comma-separated table reference list.`
```
FROM table_reference [, table_reference [, ...]]
```
There should be possibility to use more than one from mod.
But for now each next overwrite previous one.

As on the example:
```
func main() {
	qs, _ := psql.Select(
		sm.Columns("id", "name"),
		sm.From("users"),
		sm.From("clients"),
		sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
	).MustBuild()

	fmt.Println(qs)
	// Out: SELECT id, name FROM clients WHERE ("id" = $1)
	// Expected: SELECT id, name FROM users, clients WHERE ("id" = $1)
}
```

Also there should be a possibility to chain from table and from query.
As on the example:
```
func main() {
	qs, _ := psql.Select(
		sm.Columns("id", "name", "type"),
		sm.From("users"),
		sm.From(psql.Select(
			sm.Columns("id", "type"),
			sm.From("clients"),
			sm.Where(psql.Quote("client_id").EQ(psql.Arg("123"))),
		)).As("clients"),
		sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
	).MustBuild()

	fmt.Println(qs)
	// Out:
	// SELECT id, name, type
	// FROM (
	//   SELECT id, type
	//   FROM clients
	//   WHERE ("client_id" = $1)
	// ) AS "clients"
	// WHERE ("id" = $2)

	// Expected:
	// SELECT id, name, type
	// FROM users, (
	//   SELECT id, type
	//   FROM clients
	//   WHERE ("client_id" = $1)
	// ) AS "clients"
	// WHERE ("id" = $2)
}
```



Discussion:
The documentation says: 

> If more than one table reference is listed in the FROM clause, the tables are cross-joined

Since there is a different (arguably clearer) way to achieve the same result, I chose to keep things simple. You can do this instead:

```go
	psql.Select(
		sm.Columns("id", "name", "type"),
		sm.From("users").As("u"),
		sm.CrossJoin(psql.Select(
			sm.Columns("id", "type"),
			sm.From("clients"),
			sm.Where(psql.Quote("client_id").EQ(psql.Arg("123"))),
		)),
		sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
	)
```

Doing this, I realised that I left out the ability to add aliases to `CROSS JOIN`, and I'll add that soon.



Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -78,6 +78,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 - Also add the enum to the type array if an array of the enum is added. This is to prvent issues if the enum is only used in an array.
 - Handle null column names in expression indexes. (thanks @mbezhanov)
+- CROSS JOINS now allow aliases
 
 ## [v0.28.0] - 2024-06-25
 
diff --git a/dialect/mysql/dialect/mods.go b/dialect/mysql/dialect/mods.go
--- a/dialect/mysql/dialect/mods.go
+++ b/dialect/mysql/dialect/mods.go
@@ -191,11 +191,11 @@ func RightJoin[Q Joinable](e any) JoinChain[Q] {
 	return Join[Q](clause.RightJoin, e)
 }
 
-func CrossJoin[Q Joinable](e any) bob.Mod[Q] {
+func CrossJoin[Q Joinable](e any) JoinChain[Q] {
 	return Join[Q](clause.CrossJoin, e)
 }
 
-func StraightJoin[Q Joinable](e any) bob.Mod[Q] {
+func StraightJoin[Q Joinable](e any) JoinChain[Q] {
 	return Join[Q](clause.StraightJoin, e)
 }
 
diff --git a/dialect/mysql/dm/qm.go b/dialect/mysql/dm/qm.go
--- a/dialect/mysql/dm/qm.go
+++ b/dialect/mysql/dm/qm.go
@@ -68,11 +68,11 @@ func RightJoin(e any) dialect.JoinChain[*dialect.DeleteQuery] {
 	return dialect.RightJoin[*dialect.DeleteQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.DeleteQuery] {
+func CrossJoin(e any) dialect.JoinChain[*dialect.DeleteQuery] {
 	return dialect.CrossJoin[*dialect.DeleteQuery](e)
 }
 
-func StraightJoin(e any) bob.Mod[*dialect.DeleteQuery] {
+func StraightJoin(e any) dialect.JoinChain[*dialect.DeleteQuery] {
 	return dialect.StraightJoin[*dialect.DeleteQuery](e)
 }
 
diff --git a/dialect/mysql/sm/qm.go b/dialect/mysql/sm/qm.go
--- a/dialect/mysql/sm/qm.go
+++ b/dialect/mysql/sm/qm.go
@@ -71,11 +71,11 @@ func RightJoin(e any) dialect.JoinChain[*dialect.SelectQuery] {
 	return dialect.RightJoin[*dialect.SelectQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.SelectQuery] {
+func CrossJoin(e any) dialect.JoinChain[*dialect.SelectQuery] {
 	return dialect.CrossJoin[*dialect.SelectQuery](e)
 }
 
-func StraightJoin(e any) bob.Mod[*dialect.SelectQuery] {
+func StraightJoin(e any) dialect.JoinChain[*dialect.SelectQuery] {
 	return dialect.StraightJoin[*dialect.SelectQuery](e)
 }
 
diff --git a/dialect/mysql/um/qm.go b/dialect/mysql/um/qm.go
--- a/dialect/mysql/um/qm.go
+++ b/dialect/mysql/um/qm.go
@@ -44,11 +44,11 @@ func RightJoin(e any) dialect.JoinChain[*dialect.UpdateQuery] {
 	return dialect.RightJoin[*dialect.UpdateQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.UpdateQuery] {
+func CrossJoin(e any) dialect.JoinChain[*dialect.UpdateQuery] {
 	return dialect.CrossJoin[*dialect.UpdateQuery](e)
 }
 
-func StraightJoin(e any) bob.Mod[*dialect.UpdateQuery] {
+func StraightJoin(e any) dialect.JoinChain[*dialect.UpdateQuery] {
 	return dialect.StraightJoin[*dialect.UpdateQuery](e)
 }
 
diff --git a/dialect/psql/dialect/mods.go b/dialect/psql/dialect/mods.go
--- a/dialect/psql/dialect/mods.go
+++ b/dialect/psql/dialect/mods.go
@@ -123,8 +123,13 @@ func FullJoin[Q Joinable](e any) JoinChain[Q] {
 	return Join[Q](clause.FullJoin, e)
 }
 
-func CrossJoin[Q Joinable](e any) bob.Mod[Q] {
-	return Join[Q](clause.CrossJoin, e)
+func CrossJoin[Q Joinable](e any) CrossJoinChain[Q] {
+	return CrossJoinChain[Q](func() clause.Join {
+		return clause.Join{
+			Type: clause.CrossJoin,
+			To:   clause.From{Table: e},
+		}
+	})
 }
 
 type JoinChain[Q Joinable] func() clause.Join
@@ -198,6 +203,22 @@ func (j JoinChain[Q]) Using(using ...string) bob.Mod[Q] {
 	return mods.Join[Q](jo)
 }
 
+type CrossJoinChain[Q Joinable] func() clause.Join
+
+func (j CrossJoinChain[Q]) Apply(q Q) {
+	q.AppendJoin(j())
+}
+
+func (j CrossJoinChain[Q]) As(alias string, columns ...string) bob.Mod[Q] {
+	jo := j()
+	jo.To.Alias = alias
+	jo.To.Columns = columns
+
+	return CrossJoinChain[Q](func() clause.Join {
+		return jo
+	})
+}
+
 type collation struct {
 	name string
 }
diff --git a/dialect/psql/dm/qm.go b/dialect/psql/dm/qm.go
--- a/dialect/psql/dm/qm.go
+++ b/dialect/psql/dm/qm.go
@@ -58,7 +58,7 @@ func FullJoin(e any) dialect.JoinChain[*dialect.DeleteQuery] {
 	return dialect.FullJoin[*dialect.DeleteQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.DeleteQuery] {
+func CrossJoin(e any) dialect.CrossJoinChain[*dialect.DeleteQuery] {
 	return dialect.CrossJoin[*dialect.DeleteQuery](e)
 }
 
diff --git a/dialect/psql/sm/qm.go b/dialect/psql/sm/qm.go
--- a/dialect/psql/sm/qm.go
+++ b/dialect/psql/sm/qm.go
@@ -63,7 +63,7 @@ func FullJoin(e any) dialect.JoinChain[*dialect.SelectQuery] {
 	return dialect.FullJoin[*dialect.SelectQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.SelectQuery] {
+func CrossJoin(e any) dialect.CrossJoinChain[*dialect.SelectQuery] {
 	return dialect.CrossJoin[*dialect.SelectQuery](e)
 }
 
diff --git a/dialect/psql/um/qm.go b/dialect/psql/um/qm.go
--- a/dialect/psql/um/qm.go
+++ b/dialect/psql/um/qm.go
@@ -83,7 +83,7 @@ func FullJoin(e any) dialect.JoinChain[*dialect.UpdateQuery] {
 	return dialect.FullJoin[*dialect.UpdateQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.UpdateQuery] {
+func CrossJoin(e any) dialect.CrossJoinChain[*dialect.UpdateQuery] {
 	return dialect.CrossJoin[*dialect.UpdateQuery](e)
 }
 
diff --git a/dialect/sqlite/dialect/mods.go b/dialect/sqlite/dialect/mods.go
--- a/dialect/sqlite/dialect/mods.go
+++ b/dialect/sqlite/dialect/mods.go
@@ -199,6 +199,22 @@ func (j JoinChain[Q]) Using(using ...string) bob.Mod[Q] {
 	return mods.Join[Q](jo)
 }
 
+type CrossJoinChain[Q Joinable] func() clause.Join
+
+func (j CrossJoinChain[Q]) Apply(q Q) {
+	q.AppendJoin(j())
+}
+
+func (j CrossJoinChain[Q]) As(alias string, columns ...string) bob.Mod[Q] {
+	jo := j()
+	jo.To.Alias = alias
+	jo.To.Columns = columns
+
+	return CrossJoinChain[Q](func() clause.Join {
+		return jo
+	})
+}
+
 type Joinable interface{ AppendJoin(clause.Join) }
 
 func Join[Q Joinable](typ string, e any) JoinChain[Q] {
@@ -226,8 +242,13 @@ func FullJoin[Q Joinable](e any) JoinChain[Q] {
 	return Join[Q](clause.FullJoin, e)
 }
 
-func CrossJoin[Q Joinable](e any) bob.Mod[Q] {
-	return Join[Q](clause.CrossJoin, e)
+func CrossJoin[Q Joinable](e any) CrossJoinChain[Q] {
+	return CrossJoinChain[Q](func() clause.Join {
+		return clause.Join{
+			Type: clause.CrossJoin,
+			To:   clause.From{Table: e},
+		}
+	})
 }
 
 type collation struct {
diff --git a/dialect/sqlite/sm/qm.go b/dialect/sqlite/sm/qm.go
--- a/dialect/sqlite/sm/qm.go
+++ b/dialect/sqlite/sm/qm.go
@@ -45,7 +45,7 @@ func FullJoin(e any) dialect.JoinChain[*dialect.SelectQuery] {
 	return dialect.FullJoin[*dialect.SelectQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.SelectQuery] {
+func CrossJoin(e any) dialect.CrossJoinChain[*dialect.SelectQuery] {
 	return dialect.CrossJoin[*dialect.SelectQuery](e)
 }
 
diff --git a/dialect/sqlite/um/qm.go b/dialect/sqlite/um/qm.go
--- a/dialect/sqlite/um/qm.go
+++ b/dialect/sqlite/um/qm.go
@@ -91,7 +91,7 @@ func FullJoin(e any) dialect.JoinChain[*dialect.UpdateQuery] {
 	return dialect.FullJoin[*dialect.UpdateQuery](e)
 }
 
-func CrossJoin(e any) bob.Mod[*dialect.UpdateQuery] {
+func CrossJoin(e any) dialect.CrossJoinChain[*dialect.UpdateQuery] {
 	return dialect.CrossJoin[*dialect.UpdateQuery](e)
 }
 
diff --git a/website/docs/query-builder/psql/examples/select.md b/website/docs/query-builder/psql/examples/select.md
--- a/website/docs/query-builder/psql/examples/select.md
+++ b/website/docs/query-builder/psql/examples/select.md
@@ -310,3 +310,37 @@ psql.Select(
   sm.OrderBy("name").Collate("bg-BG-x-icu").Asc(),
 )
 ```
+
+## With Cross Join
+
+SQL:
+
+```sql
+SELECT id, name, type
+FROM users AS u CROSS JOIN (
+  SELECT id, type
+  FROM clients
+  WHERE ("client_id" = $1)
+) AS "clients"
+WHERE ("id" = $2)
+```
+
+Args:
+
+* `"123"`
+* `100`
+
+Code:
+
+```go
+psql.Select(
+  sm.Columns("id", "name", "type"),
+  sm.From("users").As("u"),
+  sm.CrossJoin(psql.Select(
+    sm.Columns("id", "type"),
+    sm.From("clients"),
+    sm.Where(psql.Quote("client_id").EQ(psql.Arg("123"))),
+  )).As("clients"),
+  sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
+)
+```
diff --git a/website/docs/query-builder/sqlite/examples/select.md b/website/docs/query-builder/sqlite/examples/select.md
--- a/website/docs/query-builder/sqlite/examples/select.md
+++ b/website/docs/query-builder/sqlite/examples/select.md
@@ -143,3 +143,37 @@ sqlite.Select(
   sm.OrderBy("name").Collate("NOCASE").Asc(),
 )
 ```
+
+## With Cross Join
+
+SQL:
+
+```sql
+SELECT id, name, type
+FROM users AS "u" CROSS JOIN (
+  SELECT id, type
+  FROM clients
+  WHERE ("client_id" = ?1)
+) AS "clients"
+WHERE ("id" = ?2)
+```
+
+Args:
+
+* `"123"`
+* `100`
+
+Code:
+
+```go
+sqlite.Select(
+  sm.Columns("id", "name", "type"),
+  sm.From("users").As("u"),
+  sm.CrossJoin(sqlite.Select(
+    sm.Columns("id", "type"),
+    sm.From("clients"),
+    sm.Where(sqlite.Quote("client_id").EQ(sqlite.Arg("123"))),
+  )).As("clients"),
+  sm.Where(sqlite.Quote("id").EQ(sqlite.Arg(100))),
+)
+```


Test patch:
diff --git a/dialect/psql/select_test.go b/dialect/psql/select_test.go
--- a/dialect/psql/select_test.go
+++ b/dialect/psql/select_test.go
@@ -177,6 +177,26 @@ WINDOW w AS (PARTITION BY depname ORDER BY salary)`,
 			),
 			ExpectedSQL: `SELECT id, name FROM users ORDER BY name COLLATE "bg-BG-x-icu" ASC`,
 		},
+		"with cross join": {
+			Query: psql.Select(
+				sm.Columns("id", "name", "type"),
+				sm.From("users").As("u"),
+				sm.CrossJoin(psql.Select(
+					sm.Columns("id", "type"),
+					sm.From("clients"),
+					sm.Where(psql.Quote("client_id").EQ(psql.Arg("123"))),
+				)).As("clients"),
+				sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
+			),
+			ExpectedSQL: `SELECT id, name, type
+                FROM users AS u CROSS JOIN (
+                  SELECT id, type
+                  FROM clients
+                  WHERE ("client_id" = $1)
+                ) AS "clients"
+                WHERE ("id" = $2)`,
+			ExpectedArgs: []any{"123", 100},
+		},
 	}
 
 	testutils.RunTests(t, examples, formatter)
diff --git a/dialect/sqlite/select_test.go b/dialect/sqlite/select_test.go
--- a/dialect/sqlite/select_test.go
+++ b/dialect/sqlite/select_test.go
@@ -90,6 +90,26 @@ func TestSelect(t *testing.T) {
 			),
 			ExpectedSQL: `SELECT id, name FROM users ORDER BY name COLLATE NOCASE ASC`,
 		},
+		"with cross join": {
+			Query: sqlite.Select(
+				sm.Columns("id", "name", "type"),
+				sm.From("users").As("u"),
+				sm.CrossJoin(sqlite.Select(
+					sm.Columns("id", "type"),
+					sm.From("clients"),
+					sm.Where(sqlite.Quote("client_id").EQ(sqlite.Arg("123"))),
+				)).As("clients"),
+				sm.Where(sqlite.Quote("id").EQ(sqlite.Arg(100))),
+			),
+			ExpectedSQL: `SELECT id, name, type
+                FROM users AS "u" CROSS JOIN (
+                  SELECT id, type
+                  FROM clients
+                  WHERE ("client_id" = ?1)
+                ) AS "clients"
+                WHERE ("id" = ?2)`,
+			ExpectedArgs: []any{"123", 100},
+		},
 	}
 
 	testutils.RunTests(t, examples, formatter)