querybuilder

package module
v0.5.4 Latest Latest
Warning

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

 Go to latest Published: Oct 23, 2024 License: MIT Imports: 7 Imported by: 5

README

Goal/QueryBuilder

Goal 的数据库查询构造器为创建和运行数据库查询提供了一个方便的接口。它可以用于支持大部分数据库操作，并与 Goal 支持的所有数据库系统完美运行。并且大量参考了 Laravel 的查询构造器设计，你几乎可以在这个库找到所有与 Laravel 对应的方法。

Goal 的查询构造器实现了类似 PDO 参数绑定的形式，来保护您的应用程序免受 SQL 注入攻击。因此不必清理因参数绑定而传入的字符串。查询构造器会返回你想要的 SQL 语句以及绑定参数。

运行数据库查询

根据条件从表中检索出数据

你可以使用 New() 方法来开始查询。该方法为给定的表返回一个查询构造器实例，允许你在查询上链式调用更多的约束，最后使用 get 方法获取结果：

查询语句
package tests

import (
	"fmt"
	"github.com/goal-web/contracts"
	builder "github.com/goal-web/querybuilder"
	"github.com/stretchr/testify/assert"
	"github.com/xwb1989/sqlparser"
	"testing"
)
func TestSimpleQueryBuilder(t *testing.T) {
    query := builder.New[contracts.Fields]("users")
    query.Where("name", "qbhy").
    Where("age", ">", 18).
    Where("gender", "!=", 0, contracts.Or).
    OrWhere("amount", ">=", 100).
    WhereIsNull("avatar")
    fmt.Println(query.ToSql())
    // select * from `users` where name = ? and age > ? and avatar is null or gender != ? or amount >= ?
    fmt.Println(query.GetBindings())
    // [qbhy 18 0 100]
    
    _, err := sqlparser.Parse(query.ToSql())
    assert.Nil(t, err, err)
}
插入语句

你可以通过 InsertSql 或者 CreateSql 很方便的生成插入语句。

package tests

import (
	"fmt"
	"github.com/goal-web/contracts"
	builder "github.com/goal-web/querybuilder"
	"github.com/stretchr/testify/assert"
	"github.com/xwb1989/sqlparser"
	"testing"
)

func TestInsertSql(t *testing.T) {
	sql, bindings := builder.New[contracts.Fields]("users").InsertSql([]contracts.Fields{
		{"name": "qbhy", "age": 18, "money": 100000000000, "gender": nil},
		{"name": "goal", "age": 18, "money": 10, "gender": nil},
	})
	fmt.Println(sql)
	fmt.Println(bindings)
	_, err := sqlparser.Parse(sql)
	assert.Nil(t, err, err)
}
更新语句

你可以通过 UpdateSql 很方便的生成更新语句。

package tests

import (
	"fmt"
	"github.com/goal-web/contracts"
	builder "github.com/goal-web/querybuilder"
	"github.com/stretchr/testify/assert"
	"github.com/xwb1989/sqlparser"
	"testing"
)

func TestUpdateSql(t *testing.T) {
	type Settings struct {
		Name string `json:"name"`
	}
	sql, bindings := builder.New[contracts.Fields]("users").Where("id", ">", 1).UpdateSql(contracts.Fields{
		"name": "qbhy", "age": 18, "money": 100000000000,
		"settings": Settings{Name: "json_name"},
	})
	fmt.Println(sql)
	fmt.Println(bindings)
	_, err := sqlparser.Parse(sql)
	assert.Nil(t, err, err)

	rawUpdateSql, rawUpdateBindings := builder.New[contracts.Fields]("users").
		Where("id", ">", 1).
		UpdateSql(contracts.Fields{
			"name":  "qbhy",
			"age":   builder.Expression("`age` + 10"),
			"money": 100000000000,
		})
	fmt.Println(rawUpdateSql)
	fmt.Println(rawUpdateBindings)
	_, rawUpdateErr := sqlparser.Parse(rawUpdateSql)
	assert.Nil(t, rawUpdateErr, rawUpdateErr)
}
删除语句

你可以通过 DeleteSql 很方便的生成删除语句。

package tests

import (
	"fmt"
	"github.com/goal-web/contracts"
	builder "github.com/goal-web/querybuilder"
	"github.com/stretchr/testify/assert"
	"github.com/xwb1989/sqlparser"
	"testing"
)

func TestDeleteSql(t *testing.T) {
	sql, bindings := builder.New[contracts.Fields]("users").Where("id", ">", 1).DeleteSql()
	fmt.Println(sql)
	fmt.Println(bindings)
	_, err := sqlparser.Parse(sql)
	assert.Nil(t, err, err)
}

更多高级用法

支持 where嵌套、子查询、连表、连子查询等更多高级用法

正如开头所说，你可以在这里找到几乎所有与 Laravel 对应的查询构造器方法，也可以在 测试文件 中找到更多用法

goal/query-builder
qbhy0715@qq.com

Documentation

Index

Constants

View Source 
const RandOrder contracts.OrderType = "RAND()"
View Source 
const RandomOrder contracts.OrderType = "RANDOM()"

Variables

This section is empty.

Functions

func FromQuery added in v0.2.0 

func FromQuery[T any](subQuery contracts.QueryBuilder[T], as string) contracts.QueryBuilder[T]

func FromSub 

func FromSub[T any](callback contracts.QueryProvider[T], as string) contracts.QueryBuilder[T]

func JoinStringerArray 

func JoinStringerArray(arr []fmt.Stringer, sep string) (result string)

func JoinSubStringerArray 

func JoinSubStringerArray(arr []fmt.Stringer, sep string) (result string)

func New added in v0.2.0 

func New[T any](table string) contracts.QueryBuilder[T]

Types

type Builder 

type Builder[T any] struct {
	contracts.QueryExecutor[T]

	Selects []string

	Withs []contracts.RelationType
	// contains filtered or unexported fields
}

func NewBuilder added in v0.2.0 

func NewBuilder[T any](table string) *Builder[T]

func (*Builder[T]) AddSelect 

func (builder *Builder[T]) AddSelect(fields ...string) contracts.QueryBuilder[T]

func (*Builder[T]) AddSelectSub 

func (builder *Builder[T]) AddSelectSub(provider contracts.QueryProvider[T], as string) contracts.QueryBuilder[T]

func (*Builder[T]) Bind added in v0.1.5 

func (builder *Builder[T]) Bind(executor contracts.QueryExecutor[T]) contracts.QueryBuilder[T]

func (*Builder[T]) CreateSql 

func (builder *Builder[T]) CreateSql(value contracts.Fields, insertType2 ...contracts.InsertType) (sql string, bindings []any)

func (*Builder[T]) DeleteSql 

func (builder *Builder[T]) DeleteSql() (sql string, bindings []any)

func (*Builder[T]) Distinct 

func (builder *Builder[T]) Distinct() contracts.QueryBuilder[T]

func (*Builder[T]) From 

func (builder *Builder[T]) From(table string, as ...string) contracts.QueryBuilder[T]

func (*Builder[T]) FromMany 

func (builder *Builder[T]) FromMany(tables ...string) contracts.QueryBuilder[T]

func (*Builder[T]) FromSub 

func (builder *Builder[T]) FromSub(provider contracts.QueryProvider[T], as string) contracts.QueryBuilder[T]

func (*Builder[T]) FullJoin 

func (builder *Builder[T]) FullJoin(table string, first, condition, second string) contracts.QueryBuilder[T]

func (*Builder[T]) FullOutJoin 

func (builder *Builder[T]) FullOutJoin(table string, first, condition, second string) contracts.QueryBuilder[T]

func (*Builder[T]) GetBindings 

func (builder *Builder[T]) GetBindings() (results []any)

func (*Builder[T]) GetTableName added in v0.5.2 

func (builder *Builder[T]) GetTableName() string

func (*Builder[T]) GetWith added in v0.5.2 

func (builder *Builder[T]) GetWith() []contracts.RelationType

func (*Builder[T]) GroupBy 

func (builder *Builder[T]) GroupBy(columns ...string) contracts.QueryBuilder[T]

func (*Builder[T]) Having 

func (builder *Builder[T]) Having(field string, args ...any) contracts.QueryBuilder[T]

func (*Builder[T]) InRandomOrder added in v0.1.11 

func (builder *Builder[T]) InRandomOrder(orderFunc ...contracts.OrderType) contracts.QueryBuilder[T]

func (*Builder[T]) InsertIgnoreSql 

func (builder *Builder[T]) InsertIgnoreSql(values []contracts.Fields) (sql string, bindings []any)

func (*Builder[T]) InsertReplaceSql 

func (builder *Builder[T]) InsertReplaceSql(values []contracts.Fields) (sql string, bindings []any)

func (*Builder[T]) InsertSql 

func (builder *Builder[T]) InsertSql(values []contracts.Fields, insertType2 ...contracts.InsertType) (sql string, bindings []any)

func (*Builder[T]) Join 

func (builder *Builder[T]) Join(table string, first, condition, second string, joins ...contracts.JoinType) contracts.QueryBuilder[T]

func (*Builder[T]) JoinSub 

func (builder *Builder[T]) JoinSub(provider contracts.QueryProvider[T], as, first, condition, second string, joins ...contracts.JoinType) contracts.QueryBuilder[T]

func (*Builder[T]) LeftJoin 

func (builder *Builder[T]) LeftJoin(table string, first, condition, second string) contracts.QueryBuilder[T]

func (*Builder[T]) Limit 

func (builder *Builder[T]) Limit(num int64) contracts.QueryBuilder[T]

func (*Builder[T]) Offset 

func (builder *Builder[T]) Offset(offset int64) contracts.QueryBuilder[T]

func (*Builder[T]) OrHaving 

func (builder *Builder[T]) OrHaving(field string, args ...any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhere 

func (builder *Builder[T]) OrWhere(field string, args ...any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereBetween 

func (builder *Builder[T]) OrWhereBetween(field string, args any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereExists 

func (builder *Builder[T]) OrWhereExists(provider contracts.QueryProvider[T]) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereExistsRaw added in v0.5.2 

func (builder *Builder[T]) OrWhereExistsRaw(raw string) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereFunc 

func (builder *Builder[T]) OrWhereFunc(callback contracts.QueryFunc[T]) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereIn 

func (builder *Builder[T]) OrWhereIn(field string, args any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereIsNull 

func (builder *Builder[T]) OrWhereIsNull(field string) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereNotBetween 

func (builder *Builder[T]) OrWhereNotBetween(field string, args any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereNotExists 

func (builder *Builder[T]) OrWhereNotExists(provider contracts.QueryProvider[T]) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereNotIn 

func (builder *Builder[T]) OrWhereNotIn(field string, args any) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereNotNull 

func (builder *Builder[T]) OrWhereNotNull(field string) contracts.QueryBuilder[T]

func (*Builder[T]) OrWhereRaw added in v0.5.2 

func (builder *Builder[T]) OrWhereRaw(raw string) contracts.QueryBuilder[T]

func (*Builder[T]) OrderBy 

func (builder *Builder[T]) OrderBy(field string, columnOrderType ...contracts.OrderType) contracts.QueryBuilder[T]

func (*Builder[T]) OrderByDesc 

func (builder *Builder[T]) OrderByDesc(field string) contracts.QueryBuilder[T]

func (*Builder[T]) RightJoin 

func (builder *Builder[T]) RightJoin(table string, first, condition, second string) contracts.QueryBuilder[T]

func (*Builder[T]) Select 

func (builder *Builder[T]) Select(fields ...string) contracts.QueryBuilder[T]

func (*Builder[T]) SelectForUpdateSql added in v0.1.13 

func (builder *Builder[T]) SelectForUpdateSql() (string, []any)

func (*Builder[T]) SelectSql 

func (builder *Builder[T]) SelectSql() (string, []any)

func (*Builder[T]) SelectSub 

func (builder *Builder[T]) SelectSub(provider contracts.QueryProvider[T], as string) contracts.QueryBuilder[T]

func (*Builder[T]) Skip 

func (builder *Builder[T]) Skip(offset int64) contracts.QueryBuilder[T]

func (*Builder[T]) Take 

func (builder *Builder[T]) Take(num int64) contracts.QueryBuilder[T]

func (*Builder[T]) ToSql 

func (builder *Builder[T]) ToSql() string

func (*Builder[T]) Union 

func (builder *Builder[T]) Union(b contracts.QueryBuilder[T], unionType ...contracts.UnionJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) UnionAll 

func (builder *Builder[T]) UnionAll(b contracts.QueryBuilder[T]) contracts.QueryBuilder[T]

func (*Builder[T]) UnionAllByProvider 

func (builder *Builder[T]) UnionAllByProvider(provider contracts.QueryProvider[T]) contracts.QueryBuilder[T]

func (*Builder[T]) UnionByProvider 

func (builder *Builder[T]) UnionByProvider(provider contracts.QueryProvider[T], unionType ...contracts.UnionJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) UpdateSql 

func (builder *Builder[T]) UpdateSql(value contracts.Fields) (sql string, bindings []any)

func (*Builder[T]) When 

func (builder *Builder[T]) When(condition bool, callback contracts.QueryCallback[T], elseCallback ...contracts.QueryCallback[T]) contracts.QueryBuilder[T]

func (*Builder[T]) Where 

func (builder *Builder[T]) Where(field string, args ...any) contracts.QueryBuilder[T]

func (*Builder[T]) WhereBetween 

func (builder *Builder[T]) WhereBetween(field string, args any, whereType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

WhereBetween args 参数可以是整数、浮点数、字符串、any 等类型的数组，或者用` and `隔开的字符串，或者在源码中了解更多 https://github.com/goal-web/querybuilder/blob/78bcc832604bfcdb68579e3dd1441796a16994cf/builder.go#L74

func (*Builder[T]) WhereExists 

func (builder *Builder[T]) WhereExists(provider contracts.QueryProvider[T], where ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereExistsRaw added in v0.5.2 

func (builder *Builder[T]) WhereExistsRaw(raw string, where ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereFields added in v0.1.4 

func (builder *Builder[T]) WhereFields(fields contracts.Fields) contracts.QueryBuilder[T]

func (*Builder[T]) WhereFunc 

func (builder *Builder[T]) WhereFunc(callback contracts.QueryFunc[T], whereType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereIn 

func (builder *Builder[T]) WhereIn(field string, args any, joinType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

WhereIn args 参数可以是整数、浮点数、字符串、any 等类型的数组，或者用` and `隔开的字符串，或者在源码中了解更多 https://github.com/goal-web/querybuilder/blob/78bcc832604bfcdb68579e3dd1441796a16994cf/builder.go#L74

func (*Builder[T]) WhereIsNull 

func (builder *Builder[T]) WhereIsNull(field string, whereType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereNotBetween 

func (builder *Builder[T]) WhereNotBetween(field string, args any, whereType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereNotExists 

func (builder *Builder[T]) WhereNotExists(provider contracts.QueryProvider[T], where ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereNotIn 

func (builder *Builder[T]) WhereNotIn(field string, args any, joinType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereNotNull 

func (builder *Builder[T]) WhereNotNull(field string, whereType ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) WhereRaw added in v0.5.2 

func (builder *Builder[T]) WhereRaw(raw string, whereTypes ...contracts.WhereJoinType) contracts.QueryBuilder[T]

func (*Builder[T]) With added in v0.5.2 

func (builder *Builder[T]) With(relations ...contracts.RelationType) contracts.QueryBuilder[T]

func (*Builder[T]) WithAvg added in v0.1.2 

func (builder *Builder[T]) WithAvg(field string, as ...string) contracts.QueryBuilder[T]

func (*Builder[T]) WithCount added in v0.1.2 

func (builder *Builder[T]) WithCount(fields ...string) contracts.QueryBuilder[T]

func (*Builder[T]) WithMax added in v0.1.2 

func (builder *Builder[T]) WithMax(field string, as ...string) contracts.QueryBuilder[T]

func (*Builder[T]) WithMin added in v0.1.2 

func (builder *Builder[T]) WithMin(field string, as ...string) contracts.QueryBuilder[T]

func (*Builder[T]) WithPagination 

func (builder *Builder[T]) WithPagination(perPage int64, current ...int64) contracts.QueryBuilder[T]

func (*Builder[T]) WithSum added in v0.1.2 

func (builder *Builder[T]) WithSum(field string, as ...string) contracts.QueryBuilder[T]

type Expression added in v0.1.14 

type Expression string

type GroupBy 

type GroupBy []string

func (GroupBy) IsEmpty 

func (this GroupBy) IsEmpty() bool

func (GroupBy) String 

func (this GroupBy) String() string

type Join 

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

func (Join) String 

func (join Join) String() (result string)

type Joins 

type Joins []Join

func (Joins) IsEmpty 

func (joins Joins) IsEmpty() bool

func (Joins) String 

func (joins Joins) String() (result string)

type OrderBy 

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

type OrderByFields 

type OrderByFields []OrderBy

func (OrderByFields) IsEmpty 

func (orderByFields OrderByFields) IsEmpty() bool

func (OrderByFields) String 

func (orderByFields OrderByFields) String() string

type ParamException 

type ParamException struct {
	Err       error
	Arg       any
	Condition string
	// contains filtered or unexported fields
}

func (ParamException) Error 

func (p ParamException) Error() string

func (ParamException) GetPrevious added in v0.1.18 

func (p ParamException) GetPrevious() contracts.Exception

type Unions 

type Unions[T any] map[contracts.UnionJoinType][]contracts.QueryBuilder[T]

func (Unions[T]) IsEmpty 

func (unions Unions[T]) IsEmpty() bool

func (Unions[T]) String 

func (unions Unions[T]) String() (result string)

type Where 

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

func (*Where) String 

func (wheres *Where) String() string

type Wheres 

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

func (*Wheres) IsEmpty 

func (wheres *Wheres) IsEmpty() bool

func (*Wheres) String 

func (wheres *Wheres) String() (result string)

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.