where

README

where

• Provides a fluent API for dynamically constructing SQL 'where' clauses.
• Also supports dynamic LIMIT, OFFSET and ORDER BY clauses.
• Allows the identifiers to be quoted to suit different SQL dialects, or not at all.
• dialect package supports different placeholder styles.
• quote package supports quoting SQL identifiers in back-ticks, double quotes, or nothing.

Install

Install with this command:

go get github.com/rickb777/where

Documentation

Overview

Package where provides composable expressions for WHERE and HAVING clauses in SQL. These can range from the very simplest no-op to complex nested trees of 'and' and 'or' conditions.

Also in this package are query constraints to provide 'ORDER BY', 'LIMIT' and 'OFFSET' clauses. These are similar to 'WHERE' clauses except literal values are used instead of parameter placeholders.

Index

• func Build(qc QueryConstraint, d dialect.Dialect) string
• func BuildTop(qc QueryConstraint, d dialect.Dialect) string
• func Having(wh Expression, q ...quote.Quoter) (string, []interface{})
• func Limit(n int) *queryConstraint
• func Offset(n int) *queryConstraint
• func OrderBy(column ...string) *queryConstraint
• func Where(wh Expression, q ...quote.Quoter) (string, []interface{})
• type Clause
  • func (wh Clause) And(exp Expression) Expression
  • func (wh Clause) Or(exp Expression) Expression
  • func (wh Clause) String() string
• type Condition
  • func (cl Condition) And(c2 Expression) Expression
  • func (cl Condition) Or(c2 Expression) Expression
  • func (cl Condition) String() string
• type Expression
  • func And(exp ...Expression) Expression
  • func Between(column string, a, b interface{}) Expression
  • func Eq(column string, value interface{}) Expression
  • func Gt(column string, value interface{}) Expression
  • func GtEq(column string, value interface{}) Expression
  • func In(column string, values ...interface{}) Expression
  • func Like(column string, pattern string) Expression
  • func Lt(column string, value interface{}) Expression
  • func LtEq(column string, value interface{}) Expression
  • func NoOp() Expression
  • func Not(el Expression) Expression
  • func NotEq(column string, value interface{}) Expression
  • func NotNull(column string) Expression
  • func Null(column string) Expression
  • func Or(exp ...Expression) Expression
• type QueryConstraint

Examples

• Limit
• OrderBy
• Where

Functions

func Build

func Build(qc QueryConstraint, d dialect.Dialect) string

Build builds a query constraint. It allows nil values.

func BuildTop

func BuildTop(qc QueryConstraint, d dialect.Dialect) string

BuildTop builds a query constraint as used by SQL-Server. It allows nil values. The only known dialect for which this is used is SQL-Server; otherwise it returns an empty string. Insert the returned value into your query between "SELECT [DISTINCT] " and the list of columns.

func Having

func Having(wh Expression, q ...quote.Quoter) (string, []interface{})

Having constructs the sql clause beginning "HAVING ...". It will contain '?' style placeholders; these need to be passed through the relevant quote ReplacePlaceholders processing. A quoter may optionally be supplied, otherwise the Default Quoter is used.

func Limit

func Limit(n int) *queryConstraint

Limit sets the upper limit on the number of records to be returned. The default value, 0, suppresses any limit.

Example

qc := where.Limit(10).Offset(20)

s1 := qc.Build(dialect.Sqlite)
fmt.Println("SQlite:    ", s1)

s2 := qc.BuildTop(dialect.SqlServer)
fmt.Println("SQL-Server:", s2)

s3 := qc.Build(dialect.SqlServer)
fmt.Println("SQL-Server:", s3)

Output:

SQlite:     LIMIT 10 OFFSET 20
SQL-Server: TOP (10)
SQL-Server: OFFSET 20

func Offset

func Offset(n int) *queryConstraint

Offset sets the offset into the result set; previous items will be discarded.

func OrderBy

func OrderBy(column ...string) *queryConstraint

OrderBy lists the column(s) by which the database will be asked to sort its results. The columns passed in here will be quoted according to the quoter in use when built.

Example

qc := where.OrderBy("foo", "bar").Desc().OrderBy("baz").Asc().Limit(10).Offset(20)

s := qc.Build(dialect.Sqlite)
fmt.Println(s)

Output:

ORDER BY "foo", "bar" DESC, "baz" ASC LIMIT 10 OFFSET 20

func Where

func Where(wh Expression, q ...quote.Quoter) (string, []interface{})

Where constructs the sql clause beginning "WHERE ...". It will contain '?' style placeholders; these need to be passed through the relevant quote ReplacePlaceholders processing. A quoter may optionally be supplied, otherwise the Default Quoter is used.

Example

// in this example, identifiers will be unquoted
quote.DefaultQuoter = quote.NoQuoter

// some simple expressions
nameEqJohn := where.Eq("name", "John")
nameEqPeter := where.Eq("name", "Peter")
ageGt10 := where.Gt("age", 10)
likes := where.In("likes", "cats", "dogs")

// build a compound expression - this is a static expression
// but it could be based on conditions instead
wh := where.And(where.Or(nameEqJohn, nameEqPeter), ageGt10, likes)
expr, args := where.Where(wh)

// For Postgres, the placeholders have to be altered. It's necessary to do
// this on the whole query if there might be other placeholders in it too.
expr = dialect.ReplacePlaceholdersWithNumbers(expr, "$")
fmt.Println(expr)
fmt.Println(args)

Output:

WHERE ((name=$1) OR (name=$2)) AND (age>$3) AND (likes IN ($4,$5))
[John Peter 10 cats dogs]

Types

type Clause

type Clause struct {
	// contains filtered or unexported fields
}

Clause is a compound expression.

func (Clause) And

func (wh Clause) And(exp Expression) Expression

And combines two clauses into a clause that requires they are both true. SQL implementation note: AND has higher precedence than OR.

func (Clause) Or

func (wh Clause) Or(exp Expression) Expression

Or combines two clauses into a clause that requires either is true. SQL implementation note: AND has higher precedence than OR.

func (Clause) String

func (wh Clause) String() string

type Condition

type Condition struct {
	Column, Predicate string
	Args              []interface{}
}

Condition is a simple condition such as an equality test. For convenience, use the factory functions 'Eq', 'GtEq' etc.

This can also be constructed directly, which will be useful for non-portable cases, such as Postgresql 'SIMILAR TO'

expr := where.Condition{column, " SIMILAR TO", []interface{}{pattern}}

Also for literal values (taking care to protect against injection attacks)

expr := where.Condition{column, " = 'hello'", nil}

func (Condition) And

func (cl Condition) And(c2 Expression) Expression

And combines two conditions into a clause that requires they are both true.

func (Condition) Or

func (cl Condition) Or(c2 Expression) Expression

Or combines two conditions into a clause that requires either is true.

func (Condition) String

func (cl Condition) String() string

type Expression

type Expression interface {
	fmt.Stringer
	And(Expression) Expression
	Or(Expression) Expression
	// contains filtered or unexported methods
}

Expression is an element in a WHERE clause. Expressions consist of simple conditions or more complex clauses of multiple conditions.

func And

func And(exp ...Expression) Expression

And combines some expressions into a clause that requires they are all true.

func Between

func Between(column string, a, b interface{}) Expression

Between returns a between condition on a column.

Two '?' placeholders are used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func Eq

func Eq(column string, value interface{}) Expression

Eq returns an equality condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func Gt

func Gt(column string, value interface{}) Expression

Gt returns a greater than condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func GtEq

func GtEq(column string, value interface{}) Expression

GtEq returns a greater than or equal condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func In

func In(column string, values ...interface{}) Expression

In returns an 'IN' condition on a column. * If there a no values, this becomes a no-op. * If any of the values is itself a slice or array, it is expanded to use all the contained values. * If any value is nil, an 'IS NULL' expression is OR-ed with the 'IN' expression.

Some '?' placeholders are used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func Like

func Like(column string, pattern string) Expression

Like returns a pattern-matching condition on a column. Be careful: this can hurt performance.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func Lt

func Lt(column string, value interface{}) Expression

Lt returns a less than condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func LtEq

func LtEq(column string, value interface{}) Expression

LtEq returns a less than or equal than condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func NoOp

func NoOp() Expression

NoOp creates an empty expression. This is useful for conditionally chaining expression-based contextual decisions. It can also be passed to any method that need an expression but for which none is required in that case.

func Not

func Not(el Expression) Expression

Not negates an expression.

func NotEq

func NotEq(column string, value interface{}) Expression

NotEq returns a not equal condition on a column.

A '?' placeholder is used so it may be necessary to replace placeholders in the resulting query, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

func NotNull

func NotNull(column string) Expression

NotNull returns an 'IS NOT NULL' condition on a column. It's also possible to use Not(Null(...)).

func Null

func Null(column string) Expression

Null returns an 'IS NULL' condition on a column.

func Or

func Or(exp ...Expression) Expression

Or combines some expressions into a clause that requires that any is true.

type QueryConstraint

type QueryConstraint interface {
	fmt.Stringer
	BuildTop(dialect.Dialect) string
	Build(dialect.Dialect) string
}

QueryConstraint is a value that is appended to a SELECT statement.