Repository: stephenafamo/bob (go)

Issue:
incorrect  "FilterWhere" query
I'm getting wrong SQL query using query builder for "FilterWhere".

There is extra "OVER ()" keyword in result query.

how to reproduce:

0. bob version: github.com/stephenafamo/bob v0.26.1
1. postgres 16.2
2. Table
```
create table example
(
    id bigint primary key generated always as identity,
    mac           macaddr not null
)
```
3. 
```
	selectQuery := psql.Select(
		sm.From(models.TableNames.Examples),
		sm.Columns(
			models.ColumnNames.Examples.Hostname,
			psql.F(
				"count",
				"*",
			).FilterWhere(
				models.ExampleColumns.ID.GT(psql.Arg(0)),
			).As("non_zero_ids"),
		),
		sm.GroupBy("hostname"),
	)
	queryString, _, buildErr := selectQuery.Build()
	if buildErr != nil {
		log.Fatal(buildErr)
	}
	expectedQueryString := "select hostname, count(*) filter (where id > 0) as non_zero_ids from example group by hostname"
	fmt.Println(queryString)
	fmt.Println(expectedQueryString)
```
Got:
```
SELECT 
hostname, count(*) FILTER (WHERE ("example"."id" > $1))OVER () AS "non_zero_ids"
FROM example
GROUP BY hostname
```

Expected:
```
select 
hostname, 
count(*) filter (where id > 0) as non_zero_ids
from example 
group by hostname
```


Discussion:
I'll take a look 

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -25,6 +25,13 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
     models.SelectJoins.Jets.InnerJoin.Pilots(ctx).AliasedAs("p")
     ```
 
+- Add `fm` mods to all supported dialects (psql, mysql and sqlite). These are mods for functions and are used to modify the function call. For example:
+
+  ```go
+  // import "github.com/stephenafamo/bob/dialect/psql/fm"
+  psql.F( "count", "*",)(fm.Filter(psql.Quote("status").EQ(psql.S("done"))))
+  ```
+
 ### Changed
 
 - Change the function call point for generated relationship join mods. This reduces the amount of allocations and only does the work for the relationship being used.
@@ -36,9 +43,22 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
   models.SelectJoins.Jets.InnerJoin.Pilots(ctx)
   ```
 
+- Changed how functions are modified. Instead of chained methods, the `F()` starter now returns a function which can be called with mods:
+
+  ```go
+  // Before
+  psql.F( "count", "*",).FilterWhere(psql.Quote("status").EQ(psql.S("done"))),
+  // After
+  // import "github.com/stephenafamo/bob/dialect/psql/fm"
+  psql.F( "count", "*",)(fm.Filter(psql.Quote("status").EQ(psql.S("done")))),
+  ```
+
+  This makes it possible to support more queries.
+
 ### Removed
 
 - Remove `TableWhere` function from the generated code. It was not used by the rest of the generated code and offered no clear benefit.
+- Removed `As` starter. It takes an `Expression` and is not needed since the `Expression` has an `As` method which can be used directly.
 
 ## [v0.26.1] - 2024-05-26
 
diff --git a/clause/window.go b/clause/window.go
--- a/clause/window.go
+++ b/clause/window.go
@@ -16,26 +16,26 @@ type IWindow interface {
 	SetExclusion(string)
 }
 
-type WindowDef struct {
+type Window struct {
 	From        string // an existing window name
 	orderBy     []any
 	partitionBy []any
 	Frame
 }
 
-func (wi *WindowDef) SetFrom(from string) {
+func (wi *Window) SetFrom(from string) {
 	wi.From = from
 }
 
-func (wi *WindowDef) AddPartitionBy(condition ...any) {
+func (wi *Window) AddPartitionBy(condition ...any) {
 	wi.partitionBy = append(wi.partitionBy, condition...)
 }
 
-func (wi *WindowDef) AddOrderBy(order ...any) {
+func (wi *Window) AddOrderBy(order ...any) {
 	wi.orderBy = append(wi.orderBy, order...)
 }
 
-func (wi WindowDef) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
+func (wi Window) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
 	if wi.From != "" {
 		w.Write([]byte(wi.From))
 		w.Write([]byte(" "))
diff --git a/dialect/mysql/dialect/function.go b/dialect/mysql/dialect/function.go
--- a/dialect/mysql/dialect/function.go
+++ b/dialect/mysql/dialect/function.go
@@ -8,8 +8,11 @@ import (
 	"github.com/stephenafamo/bob/expr"
 )
 
-func NewFunction(name string, args ...any) Function {
-	return Function{name: name, args: args}
+func NewFunction(name string, args ...any) *Function {
+	f := &Function{name: name, args: args}
+	f.Chain = expr.Chain[Expression, Expression]{Base: f}
+
+	return f
 }
 
 type Function struct {
@@ -17,30 +20,17 @@ type Function struct {
 	args []any
 
 	// Used in value functions. Supported by Sqlite and Postgres
-	filter []any
+	Distinct bool
+	clause.OrderBy
+	Filter []any
+	w      *clause.Window
 
 	// For chain methods
 	expr.Chain[Expression, Expression]
 }
 
-// A function can be a target for a query
-func (f *Function) Apply(q *clause.From) {
-	q.Table = f
-}
-
-func (f *Function) Filter(e ...any) *Function {
-	f.filter = append(f.filter, e...)
-
-	return f
-}
-
-func (f *Function) Over() *functionOver {
-	fo := &functionOver{
-		function: f,
-	}
-	fo.WindowChain = &WindowChain[*functionOver]{Wrap: fo}
-	fo.Base = fo
-	return fo
+func (f *Function) SetWindow(w clause.Window) {
+	f.w = &w
 }
 
 func (f Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
@@ -50,37 +40,36 @@ func (f Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error)
 
 	w.Write([]byte(f.name))
 	w.Write([]byte("("))
+
+	if f.Distinct {
+		w.Write([]byte("DISTINCT "))
+	}
+
 	args, err := bob.ExpressSlice(w, d, start, f.args, "", ", ", "")
 	if err != nil {
 		return nil, err
 	}
-	w.Write([]byte(")"))
 
-	filterArgs, err := bob.ExpressSlice(w, d, start, f.filter, " FILTER (WHERE ", " AND ", ")")
+	orderArgs, err := bob.ExpressIf(w, d, start+len(args), f.OrderBy,
+		len(f.OrderBy.Expressions) > 0, " ", "")
 	if err != nil {
 		return nil, err
 	}
-	args = append(args, filterArgs...)
-
-	return args, nil
-}
+	args = append(args, orderArgs...)
 
-type functionOver struct {
-	function *Function
-	*WindowChain[*functionOver]
-	expr.Chain[Expression, Expression]
-}
+	w.Write([]byte(")"))
 
-func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
-	fargs, err := bob.Express(w, d, start, wr.function)
+	filterArgs, err := bob.ExpressSlice(w, d, start, f.Filter, " FILTER (WHERE ", " AND ", ")")
 	if err != nil {
 		return nil, err
 	}
+	args = append(args, filterArgs...)
 
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
+	winargs, err := bob.ExpressIf(w, d, start+len(args), f.w, f.w != nil, "OVER (", ")")
 	if err != nil {
 		return nil, err
 	}
+	args = append(args, winargs...)
 
-	return append(fargs, winargs...), nil
+	return args, nil
 }
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
@@ -328,12 +328,20 @@ func (l LockChain[Q]) SkipLocked() LockChain[Q] {
 	})
 }
 
-type WindowMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
-	Name string
+type WindowMod[Q interface{ SetWindow(clause.Window) }] struct {
 	*WindowChain[*WindowMod[Q]]
 }
 
 func (w WindowMod[Q]) Apply(q Q) {
+	q.SetWindow(w.def)
+}
+
+type WindowsMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
+	Name string
+	*WindowChain[*WindowsMod[Q]]
+}
+
+func (w WindowsMod[Q]) Apply(q Q) {
 	q.AppendWindow(clause.NamedWindow{
 		Name:       w.Name,
 		Definition: w.def,
@@ -341,7 +349,7 @@ func (w WindowMod[Q]) Apply(q Q) {
 }
 
 type WindowChain[T any] struct {
-	def  clause.WindowDef
+	def  clause.Window
 	Wrap T
 }
 
diff --git a/dialect/mysql/fm/qm.go b/dialect/mysql/fm/qm.go
new file mode 100644
--- /dev/null
+++ b/dialect/mysql/fm/qm.go
@@ -0,0 +1,36 @@
+package fm
+
+import (
+	"github.com/stephenafamo/bob"
+	"github.com/stephenafamo/bob/clause"
+	"github.com/stephenafamo/bob/dialect/mysql/dialect"
+	"github.com/stephenafamo/bob/mods"
+)
+
+func Distinct() bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Distinct = true
+	})
+}
+
+func OrderBy(e any) dialect.OrderBy[*dialect.Function] {
+	return dialect.OrderBy[*dialect.Function](func() clause.OrderDef {
+		return clause.OrderDef{
+			Expression: e,
+		}
+	})
+}
+
+func Filter(e ...any) bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Filter = append(f.Filter, e...)
+	})
+}
+
+func Over() dialect.WindowMod[*dialect.Function] {
+	m := dialect.WindowMod[*dialect.Function]{}
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.Function]]{
+		Wrap: &m,
+	}
+	return m
+}
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
@@ -99,12 +99,12 @@ func WithRollup(distinct bool) bob.Mod[*dialect.SelectQuery] {
 	})
 }
 
-func Window(name string) dialect.WindowMod[*dialect.SelectQuery] {
-	m := dialect.WindowMod[*dialect.SelectQuery]{
+func Window(name string) dialect.WindowsMod[*dialect.SelectQuery] {
+	m := dialect.WindowsMod[*dialect.SelectQuery]{
 		Name: name,
 	}
 
-	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.SelectQuery]]{
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowsMod[*dialect.SelectQuery]]{
 		Wrap: &m,
 	}
 	return m
diff --git a/dialect/mysql/starters.go b/dialect/mysql/starters.go
--- a/dialect/mysql/starters.go
+++ b/dialect/mysql/starters.go
@@ -4,6 +4,7 @@ import (
 	"github.com/stephenafamo/bob"
 	"github.com/stephenafamo/bob/dialect/mysql/dialect"
 	"github.com/stephenafamo/bob/expr"
+	"github.com/stephenafamo/bob/mods"
 )
 
 type Expression = dialect.Expression
@@ -15,14 +16,16 @@ var bmod = expr.Builder[Expression, Expression]{}
 //
 //	SQL: generate_series(1, 3)
 //	Go: mysql.F("generate_series", 1, 3)
-func F(name string, args ...any) *dialect.Function {
+func F(name string, args ...any) mods.Moddable[*dialect.Function] {
 	f := dialect.NewFunction(name, args...)
 
-	// We have embedded the same function as the chain base
-	// this is so that chained methods can also be used by functions
-	f.Chain.Base = &f
+	return mods.Moddable[*dialect.Function](func(mods ...bob.Mod[*dialect.Function]) *dialect.Function {
+		for _, mod := range mods {
+			mod.Apply(f)
+		}
 
-	return &f
+		return f
+	})
 }
 
 // S creates a string literal
@@ -91,9 +94,3 @@ func Quote(ss ...string) Expression {
 func Raw(query string, args ...any) Expression {
 	return bmod.Raw(query, args...)
 }
-
-// SQL: a as "alias"
-// Go: mysql.As("a", "alias")
-func As(e Expression, alias string) bob.Expression {
-	return expr.OP("AS", e, expr.Quote(alias))
-}
diff --git a/dialect/psql/dialect/function.go b/dialect/psql/dialect/function.go
--- a/dialect/psql/dialect/function.go
+++ b/dialect/psql/dialect/function.go
@@ -4,27 +4,44 @@ import (
 	"io"
 
 	"github.com/stephenafamo/bob"
+	"github.com/stephenafamo/bob/clause"
 	"github.com/stephenafamo/bob/expr"
 )
 
-func NewFunction(name string, args ...any) Function {
-	return Function{name: name, args: args}
+func NewFunction(name string, args ...any) *Function {
+	f := &Function{name: name, args: args}
+	f.Chain = expr.Chain[Expression, Expression]{Base: f}
+
+	return f
 }
 
 type Function struct {
 	name string
 	args []any
 
-	// Used in value functions. Supported by Sqlite and Postgres
-	filter []any
+	Distinct    bool
+	WithinGroup bool
+	clause.OrderBy
+	Filter []any
+	w      *clause.Window
 
-	alias   string // used when there should be an alias before the columns
-	columns []columnDef
+	Alias   string // used when there should be an alias before the columns
+	Columns []columnDef
 
-	// For chain methods
 	expr.Chain[Expression, Expression]
 }
 
+func (f *Function) SetWindow(w clause.Window) {
+	f.w = &w
+}
+
+func (f *Function) AppendColumn(name, datatype string) {
+	f.Columns = append(f.Columns, columnDef{
+		name:     name,
+		dataType: datatype,
+	})
+}
+
 func (f *Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
 	if f.name == "" {
 		return nil, nil
@@ -32,68 +49,63 @@ func (f *Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error
 
 	w.Write([]byte(f.name))
 	w.Write([]byte("("))
+
+	if f.Distinct {
+		w.Write([]byte("DISTINCT "))
+	}
+
 	args, err := bob.ExpressSlice(w, d, start, f.args, "", ", ", "")
 	if err != nil {
 		return nil, err
 	}
+
+	if !f.WithinGroup {
+		orderArgs, err := bob.ExpressIf(w, d, start+len(args), f.OrderBy,
+			len(f.OrderBy.Expressions) > 0, " ", "")
+		if err != nil {
+			return nil, err
+		}
+		args = append(args, orderArgs...)
+	}
 	w.Write([]byte(")"))
 
-	filterArgs, err := bob.ExpressSlice(w, d, start, f.filter, " FILTER (WHERE ", " AND ", ")")
+	if f.WithinGroup {
+		orderArgs, err := bob.ExpressIf(w, d, start+len(args), f.OrderBy,
+			len(f.OrderBy.Expressions) > 0, " WITHIN GROUP (", ")")
+		if err != nil {
+			return nil, err
+		}
+		args = append(args, orderArgs...)
+	}
+
+	filterArgs, err := bob.ExpressSlice(w, d, start, f.Filter, " FILTER (WHERE ", " AND ", ")")
 	if err != nil {
 		return nil, err
 	}
 	args = append(args, filterArgs...)
 
-	if len(f.columns) > 0 || len(f.alias) > 0 {
+	if len(f.Columns) > 0 || len(f.Alias) > 0 {
 		w.Write([]byte(" AS "))
 	}
 
-	if len(f.alias) > 0 {
-		w.Write([]byte(f.alias))
+	if len(f.Alias) > 0 {
+		w.Write([]byte(f.Alias))
 		w.Write([]byte(" "))
 	}
 
-	colArgs, err := bob.ExpressSlice(w, d, start+len(args), f.columns, "(", ", ", ")")
+	colArgs, err := bob.ExpressSlice(w, d, start+len(args), f.Columns, "(", ", ", ")")
 	if err != nil {
 		return nil, err
 	}
 	args = append(args, colArgs...)
 
-	return args, nil
-}
-
-func (f *Function) FilterWhere(e ...any) *functionOver {
-	f.filter = append(f.filter, e...)
-
-	fo := &functionOver{
-		function: f,
-	}
-	fo.WindowChain = &WindowChain[*functionOver]{Wrap: fo}
-	fo.Base = fo
-	return fo
-}
-
-func (f *Function) Over() *functionOver {
-	fo := &functionOver{
-		function: f,
+	winargs, err := bob.ExpressIf(w, d, start+len(args), f.w, f.w != nil, "OVER (", ")")
+	if err != nil {
+		return nil, err
 	}
-	fo.WindowChain = &WindowChain[*functionOver]{Wrap: fo}
-	fo.Base = fo
-	return fo
-}
-
-func (f *Function) As(alias string) *Function {
-	f.alias = alias
-	return f
-}
-
-func (f *Function) Col(name, datatype string) *Function {
-	f.columns = append(f.columns, columnDef{
-		name:     name,
-		dataType: datatype,
-	})
+	args = append(args, winargs...)
 
-	return f
+	return args, nil
 }
 
 type columnDef struct {
@@ -107,26 +119,6 @@ func (c columnDef) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error
 	return nil, nil
 }
 
-type functionOver struct {
-	function *Function
-	*WindowChain[*functionOver]
-	expr.Chain[Expression, Expression]
-}
-
-func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
-	fargs, err := bob.Express(w, d, start, wr.function)
-	if err != nil {
-		return nil, err
-	}
-
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
-	if err != nil {
-		return nil, err
-	}
-
-	return append(fargs, winargs...), nil
-}
-
 type Functions []*Function
 
 func (f Functions) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
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
@@ -354,12 +354,20 @@ func (l LockChain[Q]) SkipLocked() LockChain[Q] {
 	})
 }
 
-type WindowMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
-	Name string
+type WindowMod[Q interface{ SetWindow(clause.Window) }] struct {
 	*WindowChain[*WindowMod[Q]]
 }
 
 func (w WindowMod[Q]) Apply(q Q) {
+	q.SetWindow(w.def)
+}
+
+type WindowsMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
+	Name string
+	*WindowChain[*WindowsMod[Q]]
+}
+
+func (w WindowsMod[Q]) Apply(q Q) {
 	q.AppendWindow(clause.NamedWindow{
 		Name:       w.Name,
 		Definition: w.def,
@@ -367,7 +375,7 @@ func (w WindowMod[Q]) Apply(q Q) {
 }
 
 type WindowChain[T any] struct {
-	def  clause.WindowDef
+	def  clause.Window
 	Wrap T
 }
 
diff --git a/dialect/psql/fm/qm.go b/dialect/psql/fm/qm.go
new file mode 100644
--- /dev/null
+++ b/dialect/psql/fm/qm.go
@@ -0,0 +1,54 @@
+package fm
+
+import (
+	"github.com/stephenafamo/bob"
+	"github.com/stephenafamo/bob/clause"
+	"github.com/stephenafamo/bob/dialect/psql/dialect"
+	"github.com/stephenafamo/bob/mods"
+)
+
+func Distinct() bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Distinct = true
+	})
+}
+
+func OrderBy(e any) dialect.OrderBy[*dialect.Function] {
+	return dialect.OrderBy[*dialect.Function](func() clause.OrderDef {
+		return clause.OrderDef{
+			Expression: e,
+		}
+	})
+}
+
+func WithinGroup() bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.WithinGroup = true
+	})
+}
+
+func Filter(e ...any) bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Filter = append(f.Filter, e...)
+	})
+}
+
+func Over() dialect.WindowMod[*dialect.Function] {
+	m := dialect.WindowMod[*dialect.Function]{}
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.Function]]{
+		Wrap: &m,
+	}
+	return m
+}
+
+func As(alias string) bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Alias = alias
+	})
+}
+
+func Columns(name, datatype string) bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.AppendColumn(name, datatype)
+	})
+}
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
@@ -85,12 +85,12 @@ func GroupByDistinct(distinct bool) bob.Mod[*dialect.SelectQuery] {
 	return mods.GroupByDistinct[*dialect.SelectQuery](distinct)
 }
 
-func Window(name string) dialect.WindowMod[*dialect.SelectQuery] {
-	m := dialect.WindowMod[*dialect.SelectQuery]{
+func Window(name string) dialect.WindowsMod[*dialect.SelectQuery] {
+	m := dialect.WindowsMod[*dialect.SelectQuery]{
 		Name: name,
 	}
 
-	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.SelectQuery]]{
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowsMod[*dialect.SelectQuery]]{
 		Wrap: &m,
 	}
 	return m
diff --git a/dialect/psql/starters.go b/dialect/psql/starters.go
--- a/dialect/psql/starters.go
+++ b/dialect/psql/starters.go
@@ -4,6 +4,7 @@ import (
 	"github.com/stephenafamo/bob"
 	"github.com/stephenafamo/bob/dialect/psql/dialect"
 	"github.com/stephenafamo/bob/expr"
+	"github.com/stephenafamo/bob/mods"
 )
 
 type Expression = dialect.Expression
@@ -15,14 +16,16 @@ var bmod = expr.Builder[Expression, Expression]{}
 //
 //	SQL: generate_series(1, 3)
 //	Go: psql.F("generate_series", 1, 3)
-func F(name string, args ...any) *dialect.Function {
+func F(name string, args ...any) mods.Moddable[*dialect.Function] {
 	f := dialect.NewFunction(name, args...)
 
-	// We have embedded the same function as the chain base
-	// this is so that chained methods can also be used by functions
-	f.Chain.Base = &f
+	return mods.Moddable[*dialect.Function](func(mods ...bob.Mod[*dialect.Function]) *dialect.Function {
+		for _, mod := range mods {
+			mod.Apply(f)
+		}
 
-	return &f
+		return f
+	})
 }
 
 // S creates a string literal
@@ -91,9 +94,3 @@ func Quote(ss ...string) Expression {
 func Raw(query string, args ...any) Expression {
 	return bmod.Raw(query, args...)
 }
-
-// SQL: a as "alias"
-// Go: psql.As("a", "alias")
-func As(e Expression, alias string) bob.Expression {
-	return expr.OP("AS", e, expr.Quote(alias))
-}
diff --git a/dialect/sqlite/dialect/function.go b/dialect/sqlite/dialect/function.go
--- a/dialect/sqlite/dialect/function.go
+++ b/dialect/sqlite/dialect/function.go
@@ -8,8 +8,11 @@ import (
 	"github.com/stephenafamo/bob/expr"
 )
 
-func NewFunction(name string, args ...any) Function {
-	return Function{name: name, args: args}
+func NewFunction(name string, args ...any) *Function {
+	f := &Function{name: name, args: args}
+	f.Chain = expr.Chain[Expression, Expression]{Base: f}
+
+	return f
 }
 
 type Function struct {
@@ -17,30 +20,17 @@ type Function struct {
 	args []any
 
 	// Used in value functions. Supported by Sqlite and Postgres
-	filter []any
+	Distinct bool
+	clause.OrderBy
+	Filter []any
+	w      *clause.Window
 
 	// For chain methods
 	expr.Chain[Expression, Expression]
 }
 
-// A function can be a target for a query
-func (f *Function) Apply(q *clause.From) {
-	q.Table = f
-}
-
-func (f *Function) Filter(e ...any) *Function {
-	f.filter = append(f.filter, e...)
-
-	return f
-}
-
-func (f *Function) Over() *functionOver {
-	fo := &functionOver{
-		function: f,
-	}
-	fo.WindowChain = &WindowChain[*functionOver]{Wrap: fo}
-	fo.Base = fo
-	return fo
+func (f *Function) SetWindow(w clause.Window) {
+	f.w = &w
 }
 
 func (f Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
@@ -50,37 +40,36 @@ func (f Function) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error)
 
 	w.Write([]byte(f.name))
 	w.Write([]byte("("))
+
+	if f.Distinct {
+		w.Write([]byte("DISTINCT "))
+	}
+
 	args, err := bob.ExpressSlice(w, d, start, f.args, "", ", ", "")
 	if err != nil {
 		return nil, err
 	}
-	w.Write([]byte(")"))
 
-	filterArgs, err := bob.ExpressSlice(w, d, start, f.filter, " FILTER (WHERE ", " AND ", ")")
+	orderArgs, err := bob.ExpressIf(w, d, start+len(args), f.OrderBy,
+		len(f.OrderBy.Expressions) > 0, " ", "")
 	if err != nil {
 		return nil, err
 	}
-	args = append(args, filterArgs...)
-
-	return args, nil
-}
+	args = append(args, orderArgs...)
 
-type functionOver struct {
-	function *Function
-	*WindowChain[*functionOver]
-	expr.Chain[Expression, Expression]
-}
+	w.Write([]byte(")"))
 
-func (wr *functionOver) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
-	fargs, err := bob.Express(w, d, start, wr.function)
+	filterArgs, err := bob.ExpressSlice(w, d, start, f.Filter, " FILTER (WHERE ", " AND ", ")")
 	if err != nil {
 		return nil, err
 	}
+	args = append(args, filterArgs...)
 
-	winargs, err := bob.ExpressIf(w, d, start+len(fargs), wr.def, true, "OVER (", ")")
+	winargs, err := bob.ExpressIf(w, d, start+len(args), f.w, f.w != nil, "OVER (", ")")
 	if err != nil {
 		return nil, err
 	}
+	args = append(args, winargs...)
 
-	return append(fargs, winargs...), nil
+	return args, nil
 }
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
@@ -279,12 +279,20 @@ func (o OrderBy[Q]) NullsLast() OrderBy[Q] {
 	})
 }
 
-type WindowMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
-	Name string
+type WindowMod[Q interface{ SetWindow(clause.Window) }] struct {
 	*WindowChain[*WindowMod[Q]]
 }
 
-func (w *WindowMod[Q]) Apply(q Q) {
+func (w WindowMod[Q]) Apply(q Q) {
+	q.SetWindow(w.def)
+}
+
+type WindowsMod[Q interface{ AppendWindow(clause.NamedWindow) }] struct {
+	Name string
+	*WindowChain[*WindowsMod[Q]]
+}
+
+func (w *WindowsMod[Q]) Apply(q Q) {
 	q.AppendWindow(clause.NamedWindow{
 		Name:       w.Name,
 		Definition: w.def,
@@ -292,7 +300,7 @@ func (w *WindowMod[Q]) Apply(q Q) {
 }
 
 type WindowChain[T any] struct {
-	def  clause.WindowDef
+	def  clause.Window
 	Wrap T
 }
 
diff --git a/dialect/sqlite/fm/qm.go b/dialect/sqlite/fm/qm.go
new file mode 100644
--- /dev/null
+++ b/dialect/sqlite/fm/qm.go
@@ -0,0 +1,36 @@
+package fm
+
+import (
+	"github.com/stephenafamo/bob"
+	"github.com/stephenafamo/bob/clause"
+	"github.com/stephenafamo/bob/dialect/sqlite/dialect"
+	"github.com/stephenafamo/bob/mods"
+)
+
+func Distinct() bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Distinct = true
+	})
+}
+
+func OrderBy(e any) dialect.OrderBy[*dialect.Function] {
+	return dialect.OrderBy[*dialect.Function](func() clause.OrderDef {
+		return clause.OrderDef{
+			Expression: e,
+		}
+	})
+}
+
+func Filter(e ...any) bob.Mod[*dialect.Function] {
+	return mods.QueryModFunc[*dialect.Function](func(f *dialect.Function) {
+		f.Filter = append(f.Filter, e...)
+	})
+}
+
+func Over() dialect.WindowMod[*dialect.Function] {
+	m := dialect.WindowMod[*dialect.Function]{}
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.Function]]{
+		Wrap: &m,
+	}
+	return m
+}
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
@@ -63,12 +63,12 @@ func GroupBy(e any) bob.Mod[*dialect.SelectQuery] {
 	}
 }
 
-func Window(name string) dialect.WindowMod[*dialect.SelectQuery] {
-	m := dialect.WindowMod[*dialect.SelectQuery]{
+func Window(name string) dialect.WindowsMod[*dialect.SelectQuery] {
+	m := dialect.WindowsMod[*dialect.SelectQuery]{
 		Name: name,
 	}
 
-	m.WindowChain = &dialect.WindowChain[*dialect.WindowMod[*dialect.SelectQuery]]{
+	m.WindowChain = &dialect.WindowChain[*dialect.WindowsMod[*dialect.SelectQuery]]{
 		Wrap: &m,
 	}
 	return m
diff --git a/dialect/sqlite/starters.go b/dialect/sqlite/starters.go
--- a/dialect/sqlite/starters.go
+++ b/dialect/sqlite/starters.go
@@ -4,6 +4,7 @@ import (
 	"github.com/stephenafamo/bob"
 	"github.com/stephenafamo/bob/dialect/sqlite/dialect"
 	"github.com/stephenafamo/bob/expr"
+	"github.com/stephenafamo/bob/mods"
 )
 
 type Expression = dialect.Expression
@@ -15,14 +16,16 @@ var bmod = expr.Builder[Expression, Expression]{}
 //
 //	SQL: generate_series(1, 3)
 //	Go: sqlite.F("generate_series", 1, 3)
-func F(name string, args ...any) *dialect.Function {
+func F(name string, args ...any) mods.Moddable[*dialect.Function] {
 	f := dialect.NewFunction(name, args...)
 
-	// We have embedded the same function as the chain base
-	// this is so that chained methods can also be used by functions
-	f.Chain.Base = &f
+	return mods.Moddable[*dialect.Function](func(mods ...bob.Mod[*dialect.Function]) *dialect.Function {
+		for _, mod := range mods {
+			mod.Apply(f)
+		}
 
-	return &f
+		return f
+	})
 }
 
 // S creates a string literal
@@ -91,9 +94,3 @@ func Quote(ss ...string) Expression {
 func Raw(query string, args ...any) Expression {
 	return bmod.Raw(query, args...)
 }
-
-// SQL: a as "alias"
-// Go: sqlite.As("a", "alias")
-func As(e Expression, alias string) bob.Expression {
-	return expr.OP("AS", e, expr.Quote(alias))
-}
diff --git a/mods/mods.go b/mods/mods.go
--- a/mods/mods.go
+++ b/mods/mods.go
@@ -1,6 +1,8 @@
 package mods
 
 import (
+	"io"
+
 	"github.com/stephenafamo/bob"
 	"github.com/stephenafamo/bob/clause"
 	"github.com/stephenafamo/bob/expr"
@@ -20,6 +22,14 @@ func (q QueryModFunc[T]) Apply(query T) {
 	q(query)
 }
 
+// This is a generic type for expressions can take extra mods as a function
+// allows for some fluent API, for example with functions
+type Moddable[T bob.Expression] func(...bob.Mod[T]) T
+
+func (m Moddable[T]) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
+	return m().WriteSQL(w, d, start)
+}
+
 type With[Q interface{ AppendWith(clause.CTE) }] clause.CTE
 
 func (f With[Q]) Apply(q Q) {
diff --git a/website/docs/query-builder/mysql/examples/select.md b/website/docs/query-builder/mysql/examples/select.md
--- a/website/docs/query-builder/mysql/examples/select.md
+++ b/website/docs/query-builder/mysql/examples/select.md
@@ -75,12 +75,9 @@ mysql.Select(
   sm.From(mysql.Select(
     sm.Columns(
       "status",
-      mysql.F("LEAD", "created_date", 1, mysql.F("NOW")).
-        Over().
-        PartitionBy("presale_id").
-        OrderBy("created_date").
-        Minus(mysql.Quote("created_date")).
-        As("difference")),
+      mysql.F("LEAD", "created_date", 1, mysql.F("NOW"))(
+        fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+      ).Minus(mysql.Quote("created_date")).As("difference")),
     sm.From("presales_presalestatus")),
   ).As("differnce_by_status"),
   sm.Where(mysql.Quote("status").In(mysql.S("A"), mysql.S("B"), mysql.S("C"))),
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
@@ -116,8 +116,11 @@ psql.Select(
     psql.F(
       "json_to_recordset",
       psql.Arg(`[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]`),
-    ).Col("a", "INTEGER").Col("b", "TEXT"),
-    psql.F("generate_series", 1, 3),
+    )(
+      fm.Columns("a", "INTEGER"),
+      fm.Columns("b", "TEXT"),
+    ),
+    psql.F("generate_series", 1, 3)(),
   ).As("x", "p", "q", "s"),
   sm.OrderBy("p"),
 )
@@ -149,12 +152,9 @@ psql.Select(
   sm.From(psql.Select(
     sm.Columns(
       "status",
-      psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-        Over().
-        PartitionBy("presale_id").
-        OrderBy("created_date").
-        Minus(psql.Quote("created_date")).
-        As("difference")),
+      psql.F("LEAD", "created_date", 1, psql.F("NOW"))(
+        fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+      ).Minus(psql.Quote("created_date")).As("difference")),
     sm.From("presales_presalestatus")),
   ).As("differnce_by_status"),
   sm.Where(psql.Quote("status").In(psql.S("A"), psql.S("B"), psql.S("C"))),
@@ -265,7 +265,7 @@ Code:
 ```go
 psql.Select(
   sm.Columns(
-    psql.F("row_number").Over(),
+    psql.F("row_number")(fm.Over()),
   ),
   sm.From("c"),
 )
@@ -286,7 +286,7 @@ Code:
 ```go
 psql.Select(
   sm.Columns(
-    psql.F("avg", "salary").Over().From("w"),
+    psql.F("avg", "salary")(fm.Over().From("w")),
   ),
   sm.From("c"),
   sm.Window("w").PartitionBy("depname").OrderBy("salary"),
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
@@ -91,12 +91,9 @@ sqlite.Select(
   sm.From(sqlite.Select(
     sm.Columns(
       "status",
-      sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW")).
-        Over().
-        PartitionBy("presale_id").
-        OrderBy("created_date").
-        Minus(sqlite.Quote("created_date")).
-        As("difference")),
+      sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW"))(
+        fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+      ).Minus(sqlite.Quote("created_date")).As("difference")),
     sm.From("presales_presalestatus")),
   ).As("differnce_by_status"),
   sm.Where(sqlite.Quote("status").In(sqlite.S("A"), sqlite.S("B"), sqlite.S("C"))),


Test patch:
diff --git a/dialect/mysql/select_test.go b/dialect/mysql/select_test.go
--- a/dialect/mysql/select_test.go
+++ b/dialect/mysql/select_test.go
@@ -6,6 +6,7 @@ import (
 
 	"github.com/antlr/antlr4/runtime/Go/antlr/v4"
 	"github.com/stephenafamo/bob/dialect/mysql"
+	"github.com/stephenafamo/bob/dialect/mysql/fm"
 	"github.com/stephenafamo/bob/dialect/mysql/sm"
 	testutils "github.com/stephenafamo/bob/test/utils"
 	mysqlparser "github.com/stephenafamo/sqlparser/mysql"
@@ -49,12 +50,9 @@ func TestSelect(t *testing.T) {
 				sm.From(mysql.Select(
 					sm.Columns(
 						"status",
-						mysql.F("LEAD", "created_date", 1, mysql.F("NOW")).
-							Over().
-							PartitionBy("presale_id").
-							OrderBy("created_date").
-							Minus(mysql.Quote("created_date")).
-							As("difference")),
+						mysql.F("LEAD", "created_date", 1, mysql.F("NOW"))(
+							fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+						).Minus(mysql.Quote("created_date")).As("difference")),
 					sm.From("presales_presalestatus")),
 				).As("differnce_by_status"),
 				sm.Where(mysql.Quote("status").In(mysql.S("A"), mysql.S("B"), mysql.S("C"))),
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
@@ -4,6 +4,7 @@ import (
 	"testing"
 
 	"github.com/stephenafamo/bob/dialect/psql"
+	"github.com/stephenafamo/bob/dialect/psql/fm"
 	"github.com/stephenafamo/bob/dialect/psql/sm"
 	testutils "github.com/stephenafamo/bob/test/utils"
 	pg_query "github.com/wasilibs/go-pgquery"
@@ -55,8 +56,11 @@ func TestSelect(t *testing.T) {
 					psql.F(
 						"json_to_recordset",
 						psql.Arg(`[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]`),
-					).Col("a", "INTEGER").Col("b", "TEXT"),
-					psql.F("generate_series", 1, 3),
+					)(
+						fm.Columns("a", "INTEGER"),
+						fm.Columns("b", "TEXT"),
+					),
+					psql.F("generate_series", 1, 3)(),
 				).As("x", "p", "q", "s"),
 				sm.OrderBy("p"),
 			),
@@ -87,12 +91,9 @@ func TestSelect(t *testing.T) {
 				sm.From(psql.Select(
 					sm.Columns(
 						"status",
-						psql.F("LEAD", "created_date", 1, psql.F("NOW")).
-							Over().
-							PartitionBy("presale_id").
-							OrderBy("created_date").
-							Minus(psql.Quote("created_date")).
-							As("difference")),
+						psql.F("LEAD", "created_date", 1, psql.F("NOW"))(
+							fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+						).Minus(psql.Quote("created_date")).As("difference")),
 					sm.From("presales_presalestatus")),
 				).As("differnce_by_status"),
 				sm.Where(psql.Quote("status").In(psql.S("A"), psql.S("B"), psql.S("C"))),
@@ -144,7 +145,7 @@ func TestSelect(t *testing.T) {
 			ExpectedSQL: "SELECT row_number() OVER () FROM c",
 			Query: psql.Select(
 				sm.Columns(
-					psql.F("row_number").Over(),
+					psql.F("row_number")(fm.Over()),
 				),
 				sm.From("c"),
 			),
@@ -155,7 +156,7 @@ FROM c
 WINDOW w AS (PARTITION BY depname ORDER BY salary)`,
 			Query: psql.Select(
 				sm.Columns(
-					psql.F("avg", "salary").Over().From("w"),
+					psql.F("avg", "salary")(fm.Over().From("w")),
 				),
 				sm.From("c"),
 				sm.Window("w").PartitionBy("depname").OrderBy("salary"),
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
@@ -6,6 +6,7 @@ import (
 
 	"github.com/antlr/antlr4/runtime/Go/antlr/v4"
 	"github.com/stephenafamo/bob/dialect/sqlite"
+	"github.com/stephenafamo/bob/dialect/sqlite/fm"
 	"github.com/stephenafamo/bob/dialect/sqlite/sm"
 	testutils "github.com/stephenafamo/bob/test/utils"
 	sqliteparser "github.com/stephenafamo/sqlparser/sqlite"
@@ -56,12 +57,9 @@ func TestSelect(t *testing.T) {
 				sm.From(sqlite.Select(
 					sm.Columns(
 						"status",
-						sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW")).
-							Over().
-							PartitionBy("presale_id").
-							OrderBy("created_date").
-							Minus(sqlite.Quote("created_date")).
-							As("difference")),
+						sqlite.F("LEAD", "created_date", 1, sqlite.F("NOW"))(
+							fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
+						).Minus(sqlite.Quote("created_date")).As("difference")),
 					sm.From("presales_presalestatus")),
 				).As("differnce_by_status"),
 				sm.Where(sqlite.Quote("status").In(sqlite.S("A"), sqlite.S("B"), sqlite.S("C"))),