selects

README

SelectBuilder

Part of Entiqon / Database / Builder

The SelectBuilder constructs SQL SELECT queries in Go with a fluent, safe, and dialect-aware API.
It is now part of the builder/selects subpackage.

---

✨ Features

• Define fields, source tables, joins, conditions, grouping, ordering, having, and pagination.
• Strict rules for field parsing:
  • Single string → one or many fields (comma-split).
  • "id AS alias" or "id alias" → field with alias.
  • Two args (expr, alias) → field with alias.
  • Three args (expr, alias, raw) → raw expression with alias.
  • Passing an existing Field requires .Clone(), not NewField.
• Chainable mutators (Fields, AddFields, Source, Join, Where, …).
• Safe by design: invalid tokens are carried and surfaced at Build().
• Default fallback to SELECT * if no fields are specified.
• Diagnostics: String() for concise view, Debug() for detailed state dump.

---

🚀 Quick Example

import "github.com/entiqon/entiqon/db/builder/selects"

sb := selects.New(nil).
    Fields("id", "name").
    Source("users u").
    Where("u.active = true").
    OrderBy("created_at DESC").
    Limit(10).
    Offset(20)

sql, err := sb.Build()
if err != nil {
    log.Fatal(err)
}
fmt.Println(sql)

Output:

SELECT id, name
FROM users AS u
WHERE u.active = true
ORDER BY created_at DESC
LIMIT 10 OFFSET 20

---

🔍 Usage

Fields

sb := selects.New(nil).
    Fields("id, email AS user_email")
// SELECT id, email AS user_email

Source

sb := selects.New(nil).
    Fields("id").
    Source("users u")
// SELECT id FROM users AS u

Joins

sb := selects.New(nil).
    Fields("u.id", "o.id").
    Source("users u").
    InnerJoin("users u", "orders o", "u.id = o.user_id").
    LeftJoin("orders o", "payments p", "o.id = p.order_id").
    CrossJoin("currencies c").
    NaturalJoin("states s")
// SELECT u.id, o.id FROM users AS u
// INNER JOIN orders o ON u.id = o.user_id
// LEFT JOIN payments p ON o.id = p.order_id
// CROSS JOIN currencies c
// NATURAL JOIN states s

Where

sb := selects.New(nil).
    From("users").
    Where("active = true").
    And("country = 'USA'").
    Or("role = 'admin'")
// SELECT * FROM users WHERE active = true AND country = 'USA' OR role = 'admin'

Group By / Having

sb := selects.New(nil).
    Fields("department, COUNT(*) AS total").
    Source("users").
    GroupBy("department").
    Having("COUNT(*) > 5").
    AndHaving("AVG(age) > 30")
// SELECT department, COUNT(*) AS total
// FROM users
// GROUP BY department
// HAVING COUNT(*) > 5 AND AVG(age) > 30

Order By

sb := selects.New(nil).
    Fields("id, name").
    Source("users").
    OrderBy("created_at DESC").
    ThenOrderBy("id ASC")
// SELECT id, name FROM users ORDER BY created_at DESC, id ASC

Pagination

sb := selects.New(nil).
    Source("users").
    Limit(10).
    Offset(20)
// SELECT * FROM users LIMIT 10 OFFSET 20

---

🛠 Diagnostics

• String() → concise human-readable status
• Debug() → verbose internal state with ✅/❌ markers

---

📄 License

MIT — © Entiqon Project

Documentation

Overview

Package selects provides a builder for SQL SELECT statements.

Overview

SelectBuilder constructs SELECT queries with support for:

• Fields (columns, expressions, aliases)
• Source tables (FROM)
• Joins (INNER, LEFT, RIGHT, FULL, CROSS, NATURAL)
• Conditions (WHERE)
• Grouping (GROUP BY)
• Filtering (HAVING)
• Sorting (ORDER BY)
• Pagination (LIMIT and OFFSET)

Example

sb := selects.New(nil).
    Fields("id", "name AS username").
    From("users u").
    Where("u.active = true").
    OrderBy("created_at DESC").
    Take(10)

sql, args, err := sb.Build()
if err != nil {
    log.Fatal(err)
}
fmt.Println(sql, args)

Notes

• Mutators return the builder for chaining.
• Accessors expose the current state (fields, joins, etc.).
• Invalid tokens are carried forward and surfaced at Build.
• Passing nil as dialect defaults to BaseDialect.

Index

• type SelectBuilder
  • func New(d dialect.SQLDialect) SelectBuilder

Examples

• SelectBuilder (AndHaving)
• SelectBuilder (AndWhere)
• SelectBuilder (AppendFields)
• SelectBuilder (CrossJoin)
• SelectBuilder (Fields)
• SelectBuilder (From)
• SelectBuilder (FullJoin)
• SelectBuilder (GroupBy)
• SelectBuilder (Having)
• SelectBuilder (InnerJoin)
• SelectBuilder (LeftJoin)
• SelectBuilder (NaturalJoin)
• SelectBuilder (OrHaving)
• SelectBuilder (OrWhere)
• SelectBuilder (OrderBy)
• SelectBuilder (Pagination)
• SelectBuilder (RightJoin)
• SelectBuilder (Skip)
• SelectBuilder (Take)
• SelectBuilder (ThenGroupBy)
• SelectBuilder (ThenOrderBy)
• SelectBuilder (Where)

Types

type SelectBuilder

type SelectBuilder interface {
	contract.Debuggable
	contract.Stringable

	// Fields sets the SELECT list, replacing existing fields.
	//
	// Notes:
	//   • Accepts strings, field.Token, or *field.Token.
	//   • Comma-separated strings are split into multiple fields.
	//   • Aliases may be declared with "AS" or a space.
	Fields(fields ...any) SelectBuilder

	// AppendFields adds fields to the SELECT list without clearing existing ones.
	AppendFields(fields ...any) SelectBuilder

	// GetFields returns the current SELECT fields.
	//
	// Notes:
	//   • Returns nil if no fields are defined.
	GetFields() []field.Token

	// From sets the source table.
	//
	// Notes:
	//   • Accepts strings or table.Token.
	//   • Returns an errored token if invalid.
	From(args ...any) SelectBuilder

	// Table returns the current source table token.
	//
	// Notes:
	//   • May be nil if not set.
	//   • May be errored if invalid.
	Table() table.Token

	// InnerJoin adds an INNER JOIN clause.
	InnerJoin(base any, related any, condition string) SelectBuilder

	// LeftJoin adds a LEFT JOIN clause.
	LeftJoin(base any, related any, condition string) SelectBuilder

	// RightJoin adds a RIGHT JOIN clause.
	RightJoin(base any, related any, condition string) SelectBuilder

	// FullJoin adds a FULL JOIN clause.
	FullJoin(base any, related any, condition string) SelectBuilder

	// CrossJoin adds a CROSS JOIN clause (no condition).
	CrossJoin(related any) SelectBuilder

	// NaturalJoin adds a NATURAL JOIN clause (implicit condition).
	NaturalJoin(related any) SelectBuilder

	// Joins returns all JOIN clauses.
	//
	// Notes:
	//   • Returns nil if no joins are defined.
	Joins() []join.Token

	// Where sets the WHERE conditions, replacing existing ones.
	//
	// Notes:
	//   • Accepts condition.Token, *condition.Token, or raw expressions.
	//   • Tokens preserve their declared type.
	//   • Raw expressions adopt the operator of the method used.
	Where(args ...any) SelectBuilder

	// AndWhere appends conditions combined with AND.
	AndWhere(args ...any) SelectBuilder

	// OrWhere appends conditions combined with OR.
	OrWhere(args ...any) SelectBuilder

	// Conditions returns all WHERE conditions.
	//
	// Notes:
	//   • Returns nil if none defined.
	Conditions() []condition.Token

	// GroupBy replaces the GROUP BY clause.
	//
	// Notes:
	//   • Preserves order of fields.
	//   • Passing no arguments clears groupings.
	GroupBy(fields ...string) SelectBuilder

	// ThenGroupBy appends additional GROUP BY fields.
	ThenGroupBy(fields ...string) SelectBuilder

	// Groupings returns all GROUP BY fields.
	Groupings() []string

	// OrderBy replaces the ORDER BY clause.
	//
	// Notes:
	//   • Passing no arguments clears sorting.
	//   • Use ThenOrderBy to append instead.
	OrderBy(fields ...string) SelectBuilder

	// ThenOrderBy appends additional ORDER BY fields.
	ThenOrderBy(fields ...string) SelectBuilder

	// Sorting returns all ORDER BY expressions.
	Sorting() []string

	// Having sets the HAVING clause, replacing existing conditions.
	//
	// Notes:
	//   • Accepts raw strings only.
	//   • Use AndHaving / OrHaving to append.
	Having(conditions ...string) SelectBuilder

	// AndHaving appends HAVING conditions with AND.
	AndHaving(conditions ...string) SelectBuilder

	// OrHaving appends HAVING conditions with OR.
	OrHaving(conditions ...string) SelectBuilder

	// HavingConditions returns all HAVING conditions.
	HavingConditions() []string

	// Take sets LIMIT.
	//
	// Notes:
	//   • Negative values are invalid.
	Take(value int) SelectBuilder

	// Limit returns the LIMIT value.
	//
	// Notes:
	//   • Returns 0 if unset.
	Limit() int

	// Skip sets OFFSET.
	//
	// Notes:
	//   • Negative values are invalid.
	Skip(value int) SelectBuilder

	// Offset returns the OFFSET value.
	Offset() int

	// Pagination returns LIMIT and OFFSET values.
	Pagination() (int, int)

	// Build constructs the final SQL string.
	//
	// Returns:
	//   • SQL string
	//   • Bound values
	//   • Error if invalid
	Build() (string, []interface{}, error)
}

SelectBuilder defines the contract for constructing SQL SELECT queries.

It provides methods for defining fields, source tables, joins, conditions, grouping, sorting, HAVING clauses, pagination, and building the final query. Each mutator returns the builder for chaining; accessors return the current state.

Methods:

• Fields / AppendFields / GetFields: define and retrieve the SELECT list
• From / Table: set or get the source table
• InnerJoin / LeftJoin / RightJoin / FullJoin / CrossJoin / NaturalJoin / Joins: manage JOIN clauses
• Where / AndWhere / OrWhere / Conditions: manage WHERE conditions
• GroupBy / ThenGroupBy / Groupings: manage GROUP BY expressions
• OrderBy / ThenOrderBy / Sorting: manage ORDER BY expressions
• Having / AndHaving / OrHaving / HavingConditions: manage HAVING conditions
• Take / Limit / Skip / Offset / Pagination: manage LIMIT and OFFSET
• Build: construct the final SQL string
• Debug / String: return diagnostic or human-readable views

Example (AndHaving)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("department_id, COUNT(id)").
		From("users").
		GroupBy("department_id").
		Having("COUNT(id) > 5").
		AndHaving("COUNT(id) < 100")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT department_id, COUNT(id) FROM users GROUP BY department_id HAVING COUNT(id) > 5 AND COUNT(id) < 100

Example (AndWhere)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Where("active = true").
		AndWhere("country = 'USA'")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users WHERE active = :active AND country = :country

Example (AppendFields)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Fields("id").
		AppendFields("created_at")

	sql, _, _ := sb.Build()

	fmt.Println(sql)
}

Output:
SELECT id, created_at FROM users

Example (CrossJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users u").
		CrossJoin("roles r")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users AS u CROSS JOIN roles AS r

Example (Fields)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("id").
		AppendFields("name", "username"). // expr + alias (2-args)
		From("users")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT id, name AS username FROM users

Example (From)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("id").
		From("users u")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT id FROM users AS u

Example (FullJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("customers c").
		FullJoin("customers c", "subscriptions s", "c.id = s.customer_id")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM customers AS c FULL JOIN subscriptions AS s ON c.id = s.customer_id

Example (GroupBy)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("COUNT(id)", "department").
		From("users").
		GroupBy("department")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT COUNT(id) AS department FROM users GROUP BY department

Example (Having)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("department_id, COUNT(id)").
		From("users").
		GroupBy("department_id").
		Having("COUNT(id) > 5")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT department_id, COUNT(id) FROM users GROUP BY department_id HAVING COUNT(id) > 5

Example (InnerJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("u.id, o.id").
		From("users u").
		InnerJoin("users u", "orders o", "u.id = o.user_id")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT u.id, o.id FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id

Example (LeftJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users u").
		LeftJoin("users u", "profiles p", "u.id = p.user_id")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users AS u LEFT JOIN profiles AS p ON u.id = p.user_id

Example (NaturalJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("employees e").
		NaturalJoin("departments d")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM employees AS e NATURAL JOIN departments AS d

Example (OrHaving)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("department_id, COUNT(id)").
		From("users").
		GroupBy("department_id").
		Having("COUNT(id) > 5").
		OrHaving("COUNT(id) = 1")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT department_id, COUNT(id) FROM users GROUP BY department_id HAVING COUNT(id) > 5 OR COUNT(id) = 1

Example (OrWhere)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Where("active = true").
		OrWhere("country = 'USA'")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users WHERE active = :active OR country = :country

Example (OrderBy)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("id, name").
		From("users").
		OrderBy("created_at DESC")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT id, name FROM users ORDER BY created_at DESC

Example (Pagination)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Take(10).
		Skip(20)

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users LIMIT 10 OFFSET 20

Example (RightJoin)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("orders o").
		RightJoin("orders o", "payments p", "o.id = p.order_id")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM orders AS o RIGHT JOIN payments AS p ON o.id = p.order_id

Example (Skip)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Skip(20)

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users OFFSET 20

Example (Take)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Take(10)

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users LIMIT 10

Example (ThenGroupBy)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("department, role, COUNT(id) AS collaborators").
		From("users").
		GroupBy("department").
		ThenGroupBy("role")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT department, role, COUNT(id) AS collaborators FROM users GROUP BY department, role

Example (ThenOrderBy)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
)

func main() {
	sb := selects.New(nil).
		Fields("id, name").
		From("users").
		OrderBy("created_at DESC").
		ThenOrderBy("id")

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT id, name FROM users ORDER BY created_at DESC, id

Example (Where)

package main

import (
	"fmt"

	"github.com/entiqon/db/builder/selects"
	"github.com/entiqon/db/token/types/operator"
)

func main() {
	sb := selects.New(nil).
		From("users").
		Where("age", operator.GreaterThan, 18)

	sql, _, _ := sb.Build()
	fmt.Println(sql)
}

Output:
SELECT * FROM users WHERE age > :age

func New

func New(d dialect.SQLDialect) SelectBuilder

New creates a new SelectBuilder with the provided dialect. If nil is passed, BaseDialect is used by default.