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

• Constants
• 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 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 InSlice(column string, arg interface{}) Expression
  • func Like(column string, pattern string) Expression
  • func Literal(column, predicate string, value ...interface{}) 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
  • func Limit(n int) QueryConstraint
  • func Offset(n int) QueryConstraint
  • func OrderBy(column ...string) QueryConstraint

Examples

• Limit
• OrderBy
• Where

Constants

const (
	PredicateIsNull               = " IS NULL"
	PredicateIsNotNull            = " IS NOT NULL"
	PredicateEqualTo              = "=?"
	PredicateNotEqualTo           = "<>?"
	PredicateGreaterThan          = ">?"
	PredicateGreaterThanOrEqualTo = ">=?"
	PredicateLessThan             = "<?"
	PredicateLessThanOrEqualTo    = "<=?"
	PredicateBetween              = " BETWEEN ? AND ?"
	PredicateLike                 = " LIKE ?"
)

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 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: "name", Predicate: " SIMILAR TO", Args: []interface{}{pattern}}

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

expr := where.Condition{Column: "age", Predicate: " = 47", Args: nil}

See Literal.

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. Any nil items are silently dropped.

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 value is nil, an 'IS NULL' expression is OR-ed with the 'IN' expression.

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

Note that this does not reflection, unlike InSlice.

func InSlice

func InSlice(column string, arg interface{}) Expression

InSlice returns an 'IN' condition on a column. * If arg is nil, this becomes a no-op. * arg is reflectively expanded as an array or slice 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 is necessary to replace placeholders in the resulting query according to SQL dialect, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

Note that this uses reflection, unlike In.

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 Literal

func Literal(column, predicate string, value ...interface{}) Expression

Literal returns a literal condition on a column. For example

Literal("age", " > 45")

Be careful not to allow injection attacks: do not include a string from an external source in the column or predicate.

This function is the basis for all the other predicates except In/InSlice.

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. Any nil items are silently dropped.

type QueryConstraint

type QueryConstraint interface {
	fmt.Stringer

	// 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 needs of the selected dialect.
	OrderBy(column ...string) QueryConstraint

	// Asc sets the sort order to be ascending for the columns specified previously,
	// not including those already set.
	Asc() QueryConstraint

	// Desc sets the sort order to be descending for the columns specified previously,
	// not including those already set.
	Desc() QueryConstraint

	// NullsFirst can be used to determine whether nulls appear before non-null values
	// in the sort ordering. By default, null values sort as if larger than any non-null value;
	// that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.
	NullsFirst() QueryConstraint

	// NullsLast can be used to determine whether nulls appear after non-null values
	// in the sort ordering. By default, null values sort as if larger than any non-null value;
	// that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.
	NullsLast() QueryConstraint

	// Limit sets the upper limit on the number of records to be returned.
	Limit(n int) QueryConstraint

	// Offset sets the offset into the result set; previous items will be discarded.
	Offset(n int) QueryConstraint

	// BuildTop constructs the SQL string using the given dialect. 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.
	BuildTop(dialect.Dialect) string

	// Build constructs the SQL string using the optional quoter or the default quoter.
	Build(dialect.Dialect) string
}

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

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. Be careful not to allow injection attacks: do not include a string from an external source in the columns.

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" DESC, "bar" DESC, "baz" ASC LIMIT 10 OFFSET 20