query

package module
v0.0.3 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 5, 2020 License: MIT Imports: 3 Imported by: 0

README

relation5

GoDoc Widget license Build Status

Hints

This is not an ORM.

but a really simple, yet powerful query builder, that allows you to dynamically build complex queries fast and reliable.

Not thread safe

... not yet

Currently only for MySQL-queries

... postgress and Cassandra

not production-ready

this package is not complete and not fully tested. There may be breaking changes until the final release, so use it at your own risk :)

Motivation

tbd.

Install

Requirements

Requires Go 1.10 and higher

Install with: go get -u github.com/relation5/query

Inspired by

Usage

As easy as:

import "github.com/relation5/query"
Example

Usage in combination with the great sqlx package

package user

import (
    "fmt"
    "github.com/relation5/query"
    "sort"
    "strconv"
)

func Slice(params SliceParams) {
    // [...]
    selectQuery := query.NewSelect(query.As("accounts", "a"))

    selectQuery.Columns(
        true,
        "a.uid",
        "a.given_name",
        "a.family_name",
        "a.display_name",
    )

    var (
        conditions []string
        arguments  []interface{}
    )

    if params.GroupUid != "" {
        selectQuery.Join(
            true,
            query.NewJoin(
                query.JoinTypeInner,
                query.As("group_members", "cm"),
                query.Expression("a.uid", query.OperatorEq, "cm.account_uid"),
            ),
        )
        conditions = append(conditions, query.Equal("cm.group_uid", query.PlaceholderQMark.String()))
        arguments = append(arguments, params.GroupUid)
    }

    if params.DomainUid != "" {
        selectQuery.Join(
            true,
            query.NewJoin(
                query.JoinTypeInner,
                query.As("domain_accounts", "da"),
                query.Expression("a.uid", query.OperatorEq, "da.account_uid"),
            ),
        )
        conditions = append(conditions, query.Equal("da.domain_uid", query.PlaceholderQMark.String()))
        arguments = append(arguments, params.DomainUid)
    }

    selectQuery.Where(
        query.NewPredicate(
            query.CombineAnd,
            false,
            conditions...
        ),
    )
    selectQuery.Distinct(true)
    selectQuery.OrderBy(true, query.DirectionDesc, "a.uid")

    if params.Limit > 0 {
        selectQuery.Limit(query.PlaceholderQMark.String())
        arguments = append(arguments, params.Limit)
    }

    rows, sqlErr := Database.Queryx(
        selectQuery.String(),
        arguments...,
    )

    defer func() {
        if rows != nil {
            if err := rows.Close(); err != nil {
                Logger.Error(err.Error())
            }
        }
    }()
    // [...]
}
select
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewSelect(query.As("table_name_a", "ta"))
    
    q.Columns(
        false,
        "ta.uid",
        "ta.given_name",
        "ta.family_name",
        query.As(query.Concat("ta.given_name", "' ,'", "ta.family_name"), "formatted_name"),
        "ta.primary_email",
    )
    q.Where(
        query.NewPredicate(
            query.CombineAnd,
            false,
            query.GreaterThan("ta.created_date_time", query.PlaceholderQMark.String()),
        ),
    )
    q.Limit(10)
   
    fmt.Println(q)
}

outputs:

SELECT 
    ta.uid, 
    ta.given_name, 
    ta.family_name, 
    CONCAT(ta.given_name, ' ,', ta.family_name) AS formatted_name, 
    ta.primary_email
FROM table_name_a as ta
WHERE ta.created_date_time > ?
LIMIT 1
insert
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewInsert("table_name_a")
    insertCols := []string{
        "uid",
        "given_name",
        "family_name",
        "display_name",
        "primary_email",
        "created_date_time",
    }
    q.Columns(true, insertCols...)
    q.Duplicates(
        true,
        query.Equal("uid", query.PlaceholderQMark.String()),
        query.Equal("primary_email", query.PlaceholderQMark.String()),
    )
    q.ValuesPrepared(1)

    fmt.Println(q)
}

outputs:

INSERT INTO table_name_a (
    uid, 
    given_name,
    family_name, 
    display_name, 
    primary_email, 
    created_date_time
) VALUES (?, ?, ?, ?, ?, ?)
update
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewUpdate("table_name_a")
    q.Set(
        true,
        q.Add("login_count", 2),
        q.Assign("display_name", query.PlaceholderQMark.String()),
    )
    where := query.NewPredicate(
        query.CombineAnd,
        false,
        query.LessThan("ta.created_date_time", query.PlaceholderQMark.String()),
    )

    q.Where(where)

    fmt.Println(q)
}

outputs:

UPDATE 
    table_name_a 
SET 
    login_count = login_count + 2, 
    display_name = ? 
WHERE created_date_time < ?
delete
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewDelete(query.As("table_name_a", "ta"))
    where := query.NewPredicate(
        query.CombineAnd,
        false,
        query.LessThan("ta.created_date_time", query.PlaceholderQMark.String()),
    )

    q.Where(where)
   
    fmt.Println(q)
}

outputs:

DELETE 
    FROM table_name_a AS ta 
WHERE ta.created_date_time < ?
...fun stuff :)
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewInsert("table_name_a")
    q.Ignore(false)
    insertCols := []string{
        "uid",
        "given_name",
        "family_name",
        "display_name",
        "primary_email",
        "created_date_time",
    }
    q.Columns(true, insertCols...)
    q.Duplicates(
        true,
        query.Equal("uid",  query.Values("uid")),
        query.Equal("primary_email",  query.Values("primary_email")),
    )
    q.ValuesPrepared(2)
   
    fmt.Println(q)
}

outputs:

INSERT INTO table_name_a (
    uid, 
    given_name, 
    family_name, 
    display_name, 
    primary_email, 
    created_date_time
) 
VALUES 
    (?, ?, ?, ?, ?, ?), 
    (?, ?, ?, ?, ?, ?) 
ON DUPLICATE KEY 
UPDATE 
    uid = VALUES(uid), 
    primary_email = VALUES(primary_email)
...or
package main

import (
    "fmt"
    "github.com/relation5/query"
)

func main() {
    q := query.NewSelect(query.As("table_name_a", "ta"))
    
    q.Columns(
        false,
        "ta.uid",
        "ta.given_name",
        "ta.family_name",
        query.As(query.Concat("ta.given_name", "' ,'", "ta.family_name"), "formatted_name"),
        "ta.primary_email",
        "ta.primary_email",

    )
    q.Distinct(true)    

    subSelect := query.NewSelect(query.As("table_name_a", "tc"))
    subSelect.Columns(false, "tc.uid")
    subSelect.Where(
        query.NewPredicate(
            query.CombineAnd,
            false,
            query.GreaterThan("tc.created_date_time", query.PlaceholderQMark.String()),
        ),
    )
    subSelect.Limit(10)

    q.Join(
        true,
        query.NewJoin(
            query.JoinTypeInner,
            query.As("table_name_b", "tb"),
            query.Equal("ta.uid", "tb.table_a_uid"),
            query.NotEqual("tb.parent_uid", "ta.uid"),
        ),
        query.NewJoin(
            query.JoinTypeLeftOuter,
            query.As("table_name_c", "tc"),
            query.Equal("tb.uid", "tc.table_b_uid"),
        ),
        query.NewJoin(
            query.JoinTypeInner,
            query.QueryAs(subSelect, "filter"),
            query.Equal("filter.uid", "ta.uid"),

        ),
    )

    q.Where(
        query.NewPredicate(
            query.CombineAnd,
            false,
            query.Equal("da.account_uid",   query.PlaceholderQMark.String()),
        ),
    )
    q.OrderBy(true, query.DirectionDesc, "ta.given_name", "ta.family_name")
    q.OrderBy(true, query.DirectionAsc, "ta.display_name", "ta.formatted_name")
    q.Limit(query.PlaceholderQMark.String())
    q.Offset(query.PlaceholderQMark.String())
    q.GroupBy("ta.uid")
}

outputs:

SELECT DISTINCT 
    ta.uid, 
    ta.given_name, 
    ta.family_name, 
    CONCAT(ta.given_name, ' ,', ta.family_name) AS formatted_name, 
    ta.primary_email, 
    ta.primary_email 
FROM table_name_a AS ta 
INNER JOIN table_name_b AS tb 
    ON ta.uid = tb.table_a_uid 
    AND tb.parent_uid <> ta.uid
LEFT JOIN OUTER table_name_c AS tc 
    ON tb.uid = tc.table_b_uid 
INNER JOIN (
    SELECT 
        tc.uid 
    FROM table_name_a AS tc 
    WHERE tc.created_date_time > ? 
    LIMIT 10
    ) AS filter ON filter.uid = ta.uid 
WHERE da.account_uid = ? 
ORDER BY 
    ta.given_name, 
    ta.family_name DESC, 
    ta.display_name, 
    ta.formatted_name ASC 
GROUP BY ta.uid 
LIMIT ? 
OFFSET ?

...expect more :)

Expressions

the query package comes equipped with an optional expression set, providing a suite of standard MySQL expressions. Every Expression generates a part of the SQL and can completely be replaced by a standard string

built-ins
func results in
query.As(n, a string) [...] AS [...]
query.QueryAs(query fmt.Stringer, alias string) (SELECT FROM [...] ) AS sub
query.Nested(a string) ([...])
query.Expression(a string, o Operator, b string) [...] [OPERATOR] [...]
query.ExplainQuery(q fmt.Stringer) EXPLAIN [...]
query.Like(field string, value interface{}) field LIKE %a
query.Not(expression string) NOT [...]
query.IsNull(field string) [...] IS NULL
query.IsNotNull(field string) [...] IS NOT NULL
query.Max(field string) MAX([...])
query.Min(field string) MIN([...])
query.Count(fields ...string) COUNT([...,[...]])
query.CountDistinct(fields ...string) COUNT(DISTINCT [...,[...]])
query.Coalesce(fields ...string) COALESCE([...,[...]])
query.Sum(field string) SUM([...])
query.SumDistinct(field string) SUM(DISTINCT [...])
query.Avg(field string) AVG([...])
query.AvgDistinct(field string) AVG(DISTINCT [...])
query.Concat(fields ...string) CONCAT([...,[...]])
query.Exists(query fmt.Stringer) EXISTS([...])
query.NotExists(query fmt.Stringer) NOT EXISTS([...])
query.InQuery(query fmt.Stringer) IN([...])
query.NotInQuery(query fmt.Stringer) NOT IN([...])
query.Values(field string) VALUES([...])
query.Equal(field string, value string) [...] = [...]
query.NotEqual(field string, value string) [...] <> [...]
query.GreaterThan(field string, value string) [...] > [...]
query.GreaterEqualThan(field string, value string) [...] >= [...]
query.LessThan(field string, value string) [...] < [...]
query.LessEqualThan(field string, value string) [...] =< [...]
query.AnyQuery(query fmt.Stringer) ALL ([...])
query.AllQuery(query fmt.Stringer) ANY ([...])
query.Between(field string, a interface{}, b interface{}) [...] BETWEEN [...] AND [...]
query.NotBetween(field string, a interface{}, b interface{}) [...] NOT BETWEEN [...] AND [...]
query.In(field string, values ...interface{}) IN([...,[...]])
query.NotIn(field string, values ...interface{}) NOT IN([...,[...]])

Upcoming features

  • error handling
  • other SQL-like queries
  • detailed testing
  • docs
  • better placeholder handling
  • named statements
  • ... any idea? tell us!

LICENSE

relation5 query is released under the MIT License.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func AllQuery 

func AllQuery(query fmt.Stringer) string

func AnyQuery 

func AnyQuery(query fmt.Stringer) string

func As 

func As(n, a string) string

func Avg 

func Avg(field string) string

func AvgDistinct 

func AvgDistinct(field string) string

func Between 

func Between(field string, a interface{}, b interface{}) string

func Coalesce 

func Coalesce(fields ...string) string

func Concat 

func Concat(fields ...string) string

func Count 

func Count(fields ...string) string

func CountDistinct 

func CountDistinct(fields ...string) string

func Equal 

func Equal(field string, value string) string

func Exists 

func Exists(query fmt.Stringer) string

func ExplainQuery 

func ExplainQuery(q fmt.Stringer) string

func Expression 

func Expression(a string, o Operator, b string) string

func GreaterEqualThan 

func GreaterEqualThan(field string, value string) string

func GreaterThan 

func GreaterThan(field string, value string) string

func In 

func In(field string, values ...interface{}) string

func InQuery 

func InQuery(query fmt.Stringer) string

func IsNotNull 

func IsNotNull(field string) string

func IsNull 

func IsNull(field string) string

func LessEqualThan 

func LessEqualThan(field string, value string) string

func LessThan 

func LessThan(field string, value string) string

func Like 

func Like(field string, value interface{}) string

func Max 

func Max(field string) string

func Min 

func Min(field string) string

func Nested 

func Nested(a string) string

func Not 

func Not(expression string) string

func NotBetween 

func NotBetween(field string, a interface{}, b interface{}) string

func NotEqual 

func NotEqual(field string, value string) string

func NotExists 

func NotExists(query fmt.Stringer) string

func NotIn 

func NotIn(field string, values ...interface{}) string

func NotInQuery 

func NotInQuery(query fmt.Stringer) string

func QueryAs 

func QueryAs(query fmt.Stringer, alias string) string

func Sum 

func Sum(field string) string

func SumDistinct 

func SumDistinct(field string) string

func Values 

func Values(field string) string

Types

type Combine 

type Combine int
const (
	// use “iota + 1” to be sure that the enum type is initialized.
	CombineAnd Combine = iota + 1
	CombineOr
)

func (Combine) IsValid 

func (e Combine) IsValid() bool

func (Combine) Ordinal 

func (e Combine) Ordinal() int

func (Combine) String 

func (e Combine) String() string

type DeleteQuery 

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

func NewDelete 

func NewDelete(t ...string) DeleteQuery

func (*DeleteQuery) Ignore 

func (q *DeleteQuery) Ignore(d bool)

func (*DeleteQuery) Limit 

func (q *DeleteQuery) Limit(l int)

func (*DeleteQuery) OrderBy 

func (q *DeleteQuery) OrderBy(a bool, dir Direction, c ...string)

func (DeleteQuery) String 

func (q DeleteQuery) String() string

func (*DeleteQuery) Where 

func (q *DeleteQuery) Where(exp Predicate)

type Direction 

type Direction int
const (
	// use “iota + 1” to be sure that the enum type is initialized.
	DirectionAsc Direction = iota + 1
	DirectionDesc
)

func (Direction) IsValid 

func (e Direction) IsValid() bool

func (Direction) Ordinal 

func (e Direction) Ordinal() int

func (Direction) String 

func (e Direction) String() string

type InsertQuery 

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

func NewInsert 

func NewInsert(t string) InsertQuery

func (*InsertQuery) Columns 

func (q *InsertQuery) Columns(a bool, c ...string)

func (*InsertQuery) Duplicates 

func (q *InsertQuery) Duplicates(a bool, d ...string)

func (*InsertQuery) Ignore 

func (q *InsertQuery) Ignore(d bool)

func (InsertQuery) String 

func (q InsertQuery) String() string

func (*InsertQuery) Values 

func (q *InsertQuery) Values(a bool, values ...interface{}) error

func (*InsertQuery) ValuesPrepared 

func (q *InsertQuery) ValuesPrepared(rows int)

type Join 

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

func NewJoin 

func NewJoin(j JoinType, t string, p ...string) Join

func (Join) String 

func (p Join) String() string

type JoinType 

type JoinType int
const (
	// use “iota + 1” to be sure that the enum type is initialized.
	JoinTypeBase JoinType = iota + 1
	JoinTypeLeft
	JoinTypeLeftOuter
	JoinTypeRight
	JoinTypeRightOuter
	JoinTypeInner
	JoinTypeFull
	JoinTypeFullOuter
	JoinTypeNatural
	JoinTypeCross
)

func (JoinType) IsValid 

func (e JoinType) IsValid() bool

func (JoinType) Ordinal 

func (e JoinType) Ordinal() int

func (JoinType) String 

func (e JoinType) String() string

type Operator 

type Operator int
const (
	// use “iota + 1” to be sure that the enum type is initialized.
	OperatorEq Operator = iota + 1
	OperatorNotEq
	OperatorGt
	OperatorGtEq
	OperatorLt
	OperatorLtEq
)

func (Operator) IsValid 

func (e Operator) IsValid() bool

func (Operator) Ordinal 

func (e Operator) Ordinal() int

func (Operator) String 

func (e Operator) String() string

type Placeholder 

type Placeholder int
const (
	// use “iota + 1” to be sure that the enum type is initialized.
	PlaceholderQMark Placeholder = iota + 1
	PlaceholderDollar
)

func (Placeholder) IsValid 

func (e Placeholder) IsValid() bool

func (Placeholder) Ordinal 

func (e Placeholder) Ordinal() int

func (Placeholder) String 

func (e Placeholder) String() string

type Predicate 

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

func NewPredicate 

func NewPredicate(c Combine, nested bool, ex ...string) Predicate

func (Predicate) String 

func (p Predicate) String() string

type SelectQuery 

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

func NewSelect 

func NewSelect(t string) SelectQuery

func (*SelectQuery) Columns 

func (q *SelectQuery) Columns(a bool, c ...string)

func (*SelectQuery) Default 

func (q *SelectQuery) Default(s string)

func (*SelectQuery) Distinct 

func (q *SelectQuery) Distinct(d bool)

func (*SelectQuery) GroupBy 

func (q *SelectQuery) GroupBy(c ...string)

func (*SelectQuery) Having 

func (q *SelectQuery) Having(exp Predicate)

func (*SelectQuery) Join 

func (q *SelectQuery) Join(a bool, j ...Join)

func (*SelectQuery) Limit 

func (q *SelectQuery) Limit(v interface{})

func (*SelectQuery) Offset 

func (q *SelectQuery) Offset(v interface{})

func (*SelectQuery) OrderBy 

func (q *SelectQuery) OrderBy(a bool, dir Direction, c ...string)

func (SelectQuery) String 

func (q SelectQuery) String() string

func (*SelectQuery) Union 

func (q *SelectQuery) Union(a bool, u ...SelectQuery)

func (*SelectQuery) Using 

func (q *SelectQuery) Using(a bool, u ...string)

func (*SelectQuery) Where 

func (q *SelectQuery) Where(exp Predicate)

type UpdateQuery 

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

func NewUpdate 

func NewUpdate(t ...string) UpdateQuery

func (*UpdateQuery) Add 

func (q *UpdateQuery) Add(field string, value interface{}) string

func (*UpdateQuery) Assign 

func (q *UpdateQuery) Assign(field string, value interface{}) string

func (*UpdateQuery) Decrement 

func (q *UpdateQuery) Decrement(field string) string

func (*UpdateQuery) Divide 

func (q *UpdateQuery) Divide(field string, value interface{}) string

func (*UpdateQuery) Ignore 

func (q *UpdateQuery) Ignore(d bool)

func (*UpdateQuery) Increment 

func (q *UpdateQuery) Increment(field string) string

func (*UpdateQuery) Multiply 

func (q *UpdateQuery) Multiply(field string, value interface{}) string

func (*UpdateQuery) Set 

func (q *UpdateQuery) Set(a bool, assignments ...string)

func (*UpdateQuery) SetPrepared 

func (q *UpdateQuery) SetPrepared(a bool, fields ...string)

func (UpdateQuery) String 

func (q UpdateQuery) String() string

func (*UpdateQuery) Subtract 

func (q *UpdateQuery) Subtract(field string, value interface{}) string

func (*UpdateQuery) Where 

func (q *UpdateQuery) Where(exp Predicate)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.