q: github.com/oov/q Index | Examples | Files | Directories

package q

import "github.com/oov/q"

Package q implements a SQL builder.

Code:

user := q.T("user")
sel := q.Select().From(
    user,
).Column(
    user.C("id"),
    user.C("name"),
).Where(
    q.Eq(user.C("age"), 18),
)
// You can use sel by performing the following steps.
// sql, args := sel.ToSQL()
// rows, err := db.Query(sql, args...)
// ...
fmt.Println(sel)

Output:

SELECT "user"."id", "user"."name" FROM "user" WHERE "user"."age" = ? [18]

Code:

user := q.T("user", "u")
age := user.C("age")
sel := q.Select().From(user).Column(
    q.Sum(
        q.Case().When(
            q.And(
                q.Gte(age, 13),
                q.Lte(age, 19),
            ),
            1,
        ).Else(0),
    ).C("teen"),
    q.Sum(q.Case().When(q.Gte(age, 20), 1).Else(0)).C("adult"),
)
fmt.Println(sel)

Output:

SELECT SUM(CASE WHEN ("u"."age" >= ?)AND("u"."age" <= ?) THEN ? ELSE ? END) AS "teen", SUM(CASE WHEN "u"."age" >= ? THEN ? ELSE ? END) AS "adult" FROM "user" AS "u" [13 19 1 0 20 1 0]

Code:

user := q.T("user")
sel := q.Select().From(
    user,
).Column(
    user.C("id"),
    user.C("name"),
).Where(
    q.Eq(user.C("age"), q.V(18, "findAge")),
)
// You can use sel by performing the following steps.
// sql, argsBuilderGenerator := sel.ToPrepared()
// stmt, err := db.Prepare(sql)
// ...
// ab := argsBuilderGenerator()
// ab.Set("findAge", 24)
// rows, err := stmt.Query(ab.Args...)
fmt.Println(sel)

Output:

SELECT "user"."id", "user"."name" FROM "user" WHERE "user"."age" = ? [18]

Code:

user := q.T("user", "u")
age := user.C("age")
sel := q.Select().From(user).Column(
    q.Unsafe(`SUM(CASE WHEN (`, age, ` >= 13)AND(`, age, ` <= 19) THEN 1 ELSE 0 END)`).C("teen"),
    q.Unsafe(`SUM(CASE WHEN `, age, ` >= 20 THEN 1 ELSE 0 END)`).C("adult"),
)
fmt.Println(sel)

lastName := user.C("last_name")
maliciousUserInput := "' OR '' = '"
sel = q.Select().From(user).Where(
    // Safe
    q.Eq(lastName, maliciousUserInput),
    // Safe
    q.Unsafe(lastName, ` = `, q.V(maliciousUserInput)),
    // Unsafe - DO NOT THIS
    q.Unsafe(lastName, ` = '`, maliciousUserInput, `'`),
)
fmt.Println(sel)

Output:

SELECT SUM(CASE WHEN ("u"."age" >= 13)AND("u"."age" <= 19) THEN 1 ELSE 0 END) AS "teen", SUM(CASE WHEN "u"."age" >= 20 THEN 1 ELSE 0 END) AS "adult" FROM "user" AS "u" []
SELECT * FROM "user" AS "u" WHERE ("u"."last_name" = ?)AND("u"."last_name" = ?)AND("u"."last_name" = '' OR '' = '') [' OR '' = ' ' OR '' = ']

Index

Examples

Package Files

args.go case.go column.go delete.go dialect.go expr.go exprs.go func.go insert.go interval.go intervals.go select.go table.go update.go util.go

Variables

var (
    // DefaultDialect is default setting of builder's dialect.
    // When not set a Dialect in builder, this value is used.
    DefaultDialect qutil.Dialect

    // MySQL implements a dialect in MySQL.
    MySQL = qutil.MySQL
    // PostgreSQL implements a dialect in PostgreSQL.
    PostgreSQL = qutil.PostgreSQL
    // SQLite implements a dialect in SQLite.
    SQLite = qutil.SQLite
)

type Column Uses

type Column interface {
    Expression

    // for internal use.
    WriteColumn(ctx *qutil.Context, buf []byte) []byte
    WriteDefinition(ctx *qutil.Context, buf []byte) []byte
}

Column represents database table column. You can create it from C or Table.C or Expression.C.

Code:

fmt.Println("q.C(name):               ", q.C("id"))
fmt.Println("q.C(name, alias):        ", q.C("age", "ag"))
fmt.Println("Table.C(name):           ", q.T("user").C("age"))
fmt.Println("Table.C(name, alias):    ", q.T("user").C("age", "ag"))
fmt.Println("Expression.C():          ", q.CountAll().C())
fmt.Println("Expression.C(alias):     ", q.CountAll().C("cnt"))

country := q.T("country")
sel := q.Select().Column(
    country.C("name"),
).From(
    country,
).Where(
    q.Eq(country.C("id"), 100),
)
fmt.Println("*ZSelectBuilder.C():     ", sel.C())
fmt.Println("*ZSelectBuilder.C(alias):", sel.C("cname"))

Output:

q.C(name):                "id" []
q.C(name, alias):         "age" AS "ag" []
Table.C(name):            "user"."age" []
Table.C(name, alias):     "user"."age" AS "ag" []
Expression.C():           COUNT(*) []
Expression.C(alias):      COUNT(*) AS "cnt" []
*ZSelectBuilder.C():      (SELECT "country"."name" FROM "country" WHERE "country"."id" = ?) [100]
*ZSelectBuilder.C(alias): (SELECT "country"."name" FROM "country" WHERE "country"."id" = ?) AS "cname" [100]

func C Uses

func C(columnName string, aliasName ...string) Column

C creates Column.

This is an example of how to use C. Actually, using Table.C is more useful in many cases than using C directly because Table.C adds the table name before column name.

Code:

fmt.Println("name:        ", q.C("id"))
fmt.Println("name + alias:", q.C("age", "ag"))

Output:

name:         "id" []
name + alias: "age" AS "ag" []

type Expression Uses

type Expression interface {
    // C creates Column from Expression.
    C(aliasName ...string) Column
    // for internal use.
    WriteExpression(ctx *qutil.Context, buf []byte) []byte
}

Expression represents expressions.

This is an example of how to use Expression.

Code:

id := q.C("id")
fmt.Println("Eq(id, 100):     ", q.Eq(id, 100))
fmt.Println("Eq(id, nil):     ", q.Eq(id, nil))
fmt.Println("Eq(id, []int):   ", q.Eq(id, []int{1, 2}))
fmt.Println("In(id, []int):   ", q.In(id, []int{1, 2}))
fmt.Println("Neq(id, 100):    ", q.Neq(id, 100))
fmt.Println("Neq(id, nil):    ", q.Neq(id, nil))
fmt.Println("Neq(id, []int):  ", q.Neq(id, []int{1, 2}))
fmt.Println("NotIn(id, []int):", q.NotIn(id, []int{1, 2}))
fmt.Println("Gt(id, 100):     ", q.Gt(id, 100))
fmt.Println("Gte(id, 100):    ", q.Gte(id, 100))
fmt.Println("Lt(id, 100):     ", q.Lt(id, 100))
fmt.Println("Lte(id, 100):    ", q.Lte(id, 100))

Output:

Eq(id, 100):      "id" = ? [100]
Eq(id, nil):      "id" IS NULL []
Eq(id, []int):    "id" IN (?,?) [1 2]
In(id, []int):    "id" IN (?,?) [1 2]
Neq(id, 100):     "id" != ? [100]
Neq(id, nil):     "id" IS NOT NULL []
Neq(id, []int):   "id" NOT IN (?,?) [1 2]
NotIn(id, []int): "id" NOT IN (?,?) [1 2]
Gt(id, 100):      "id" > ? [100]
Gte(id, 100):     "id" >= ? [100]
Lt(id, 100):      "id" < ? [100]
Lte(id, 100):     "id" <= ? [100]

func Eq Uses

func Eq(l, r interface{}) Expression

Eq creates Expression such as "l = r". But when you pass nil to one of a pair, Eq creates "x IS NULL" instead. In the same way, when you pass slice of any type to r, Eq creates "x IN (?)".

func Gt Uses

func Gt(l, r interface{}) Expression

Gt creates Expression such as "l > r".

func Gte Uses

func Gte(l, r interface{}) Expression

Gte creates Expression such as "l >= r".

func In Uses

func In(l, r interface{}) Expression

In creates Expression such as "l IN r".

func Lt Uses

func Lt(l, r interface{}) Expression

Lt creates Expression such as "l < r".

func Lte Uses

func Lte(l, r interface{}) Expression

Lte creates Expression such as "l <= r".

func Neq Uses

func Neq(l, r interface{}) Expression

Neq creates Expression such as "l != r". But when you pass nil to one of a pair, Neq creates "x IS NOT NULL" instead. In the same way, when you pass slice of any type to r, Neq creates "x NOT IN (?)".

func NotIn Uses

func NotIn(l, r interface{}) Expression

NotIn creates Expression such as "l NOT IN r".

type Expressions Uses

type Expressions Expression

Expressions represents combination of an expression.

This is an example of how to use Expressions.

Code:

user := q.T("user")
fmt.Println("And:       ", q.And(
    q.Eq(user.C("age"), 15),
    q.Eq(user.C("gender"), "female"),
    q.Eq(user.C("nickname"), "Shipon"),
))
fmt.Println("Or:        ", q.Or(
    q.Neq(user.C("name"), nil),
    q.Neq(user.C("nickname"), nil),
))
fmt.Println("And(empty):", q.And())
fmt.Println("Or(empty): ", q.Or())

Output:

And:        ("user"."age" = ?)AND("user"."gender" = ?)AND("user"."nickname" = ?) [15 female Shipon]
Or:         ("user"."name" IS NOT NULL)OR("user"."nickname" IS NOT NULL) []
And(empty): ('empty' = 'AND') []
Or(empty):  ('empty' = 'OR') []

func And Uses

func And(exprs ...Expression) Expressions

And creates Expression such as "(exprs[0])AND(exprs[1])AND(exprs[2])".

If you output expression which isn't adding Expression at all, it generates "('empty' = 'AND')".

func Or Uses

func Or(exprs ...Expression) Expressions

Or creates Expression such as "(exprs[0])OR(exprs[1])OR(exprs[2])".

If you output expression which isn't adding Expression at all, it generates "('empty' = 'OR')".

type Function Uses

type Function Expression

Function represents functions.

func AddInterval Uses

func AddInterval(v interface{}, intervals ...Interval) Function

AddInterval creates Function such as "v + INTERVAL intervals[n] YEAR + ...".

func Avg Uses

func Avg(v interface{}) Function

Avg creates Function such as "AVG(v)".

func CharLength Uses

func CharLength(v interface{}) Function

CharLength creates Function such as "CHAR_LENGTH(v)".

func Count Uses

func Count(v interface{}) Function

Count creates Function such as "COUNT(v)".

func CountAll Uses

func CountAll() Function

CountAll creates Function "COUNT(*)".

func Max Uses

func Max(v interface{}) Function

Max creates Function such as "MAX(v)".

func Min Uses

func Min(v interface{}) Function

Min creates Function such as "MIN(v)".

func Now Uses

func Now() Function

Now creates Function "CURRENT_TIMESTAMP".

func Sum Uses

func Sum(v interface{}) Function

Sum creates Function such as "SUM(v)".

type Interval Uses

type Interval interface {
    Value() int
    Unit() qutil.IntervalUnit
}

Interval represents intervals in SQL statements.

func Days Uses

func Days(n int) Interval

Days creates Interval such as "INTERVAL n DAY".

func Hours Uses

func Hours(n int) Interval

Hours creates Interval such as "INTERVAL n HOUR".

func Minutes Uses

func Minutes(n int) Interval

Minutes creates Interval such as "INTERVAL n MINUTE".

func Months Uses

func Months(n int) Interval

Months creates Interval such as "INTERVAL n MONTH".

func Seconds Uses

func Seconds(n int) Interval

Seconds creates Interval such as "INTERVAL n SECOND".

func Years Uses

func Years(n int) Interval

Years creates Interval such as "INTERVAL n YEAR".

type Table Uses

type Table interface {
    C(columnName string, aliasName ...string) Column

    InnerJoin(table Table, conds ...Expression) Table
    LeftJoin(table Table, conds ...Expression) Table
    CrossJoin(table Table) Table

    JoinIndex(i int) (string, Table, Expressions)
    JoinLen() int

    // for internal use.
    WriteTable(ctx *qutil.Context, buf []byte) []byte
    WriteJoins(ctx *qutil.Context, buf []byte) []byte
    WriteDefinition(ctx *qutil.Context, buf []byte) []byte
}

Table represents database table. You can create it from T or *ZSelectBuilder.T.

This is an example of how to use Table.InnerJoin.

Code:

user := q.T("user", "usr")
post := q.T("post", "pst")
// user.id -> post.user_id
user.InnerJoin(post, q.Eq(user.C("id"), post.C("user_id")))
fmt.Println("Short:", user)

postTag := q.T("posttag", "rel")
tag := q.T("tag", "tg")
// post.id -> posttag.post_id
post.InnerJoin(postTag, q.Eq(post.C("id"), postTag.C("post_id")))
// posttag.tag_id -> tag.id
postTag.InnerJoin(tag, q.Eq(postTag.C("tag_id"), tag.C("id")))
fmt.Println("Long: ", user)

Output:

Short: "user" AS "usr" INNER JOIN "post" AS "pst" ON "usr"."id" = "pst"."user_id" []
Long:  "user" AS "usr" INNER JOIN ("post" AS "pst" INNER JOIN ("posttag" AS "rel" INNER JOIN "tag" AS "tg" ON "rel"."tag_id" = "tg"."id") ON "pst"."id" = "rel"."post_id") ON "usr"."id" = "pst"."user_id" []

func T Uses

func T(tableName string, aliasName ...string) Table

T creates Table.

This is an example of how to use T.

Code:

fmt.Println("name:        ", q.T("user"))
fmt.Println("name + alias:", q.T("user", "usr"))

Output:

name:         "user" []
name + alias: "user" AS "usr" []

type UnsafeExpression Uses

type UnsafeExpression Expression

UnsafeExpression represents unsafe expression.

func Unsafe Uses

func Unsafe(v ...interface{}) UnsafeExpression

Unsafe creates any custom expressions.

But IT DOES NOT ESCAPE so if want to use input from outside, should wrap by V or InV.

The basic how to use is similar to fmt.Print. Please refer to the example for more details.

This is an example of how to use Unsafe, V and InV.

Code:

user := q.T("user")
id, name, age := user.C("id"), user.C("name"), user.C("age")
expr := q.Unsafe(
    "(", id, " % 2 = 1)AND",
    "(", name, " != ", q.V("yourname"), ")AND",
    "(", age, " IN ", q.InV([]int{16, 17, 18}), ")",
)
fmt.Println(expr)

Output:

("user"."id" % 2 = 1)AND("user"."name" != ?)AND("user"."age" IN (?,?,?)) [yourname 16 17 18]

type Variable Uses

type Variable Expression

Variable represents the argument to which is given from outside.

func InV Uses

func InV(slice interface{}) Variable

InV creates Variable from slice. It can be used with IN operator.

func V Uses

func V(v interface{}, aliasForPrepared ...interface{}) Variable

V creates Variable from a single input.

If aliasForPrepared is given, the value can be rewritten using an alias name in ZArgsBuilder.Set. Please refer to the Select.ToPrepared example for more details.

type ZAndExpr Uses

type ZAndExpr []Expression

ZAndExpr represents AND Expression.

func (ZAndExpr) C Uses

func (e ZAndExpr) C(aliasName ...string) Column

C implements Expression interface method.

func (ZAndExpr) String Uses

func (e ZAndExpr) String() string

String implements fmt.Stringer interface method.

func (ZAndExpr) WriteExpression Uses

func (e ZAndExpr) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface method.

type ZArgsBuilder Uses

type ZArgsBuilder struct {
    Args []interface{}
    // contains filtered or unexported fields
}

ZArgsBuilder is query arguments builder.

func (*ZArgsBuilder) Set Uses

func (b *ZArgsBuilder) Set(key, value interface{})

Set sets the index entries associated with key to the single element value.

type ZCaseBuilder Uses

type ZCaseBuilder struct {
    Base     Expression
    WhenThen [][2]Expression
    ElseThen Expression
}

ZCaseBuilder implements a CASE expression builder. This also implements Expression interface, so it can use in many place.

func Case Uses

func Case(base ...Expression) *ZCaseBuilder

Case creates ZCaseBuilder. If omitting an argument, it'll be a searched CASE builder.

Simple CASE:
	CASE base WHEN 0 THEN 'false' THEN 1 THEN 'true' END
Searched CASE:
	CASE WHEN base = 0 THEN 'false' THEN base = 1 THEN 'true' END

This is an example of how to use Case.

Code:

user := q.T("user")
cs := q.Case().When(
    q.Eq(user.C("id"), 100),
    10,
).Else(
    0,
)
fmt.Println(cs)
fmt.Println(q.Select().From(user).Column(cs.C("bonus")))

Output:

CASE WHEN "user"."id" = ? THEN ? ELSE ? END [100 10 0]
SELECT CASE WHEN "user"."id" = ? THEN ? ELSE ? END AS "bonus" FROM "user" [100 10 0]

func (*ZCaseBuilder) C Uses

func (b *ZCaseBuilder) C(aliasName ...string) Column

C implements Expression interface.

func (*ZCaseBuilder) Else Uses

func (b *ZCaseBuilder) Else(then interface{}) *ZCaseBuilder

Else sets "ELSE then" to the builder.

func (*ZCaseBuilder) String Uses

func (b *ZCaseBuilder) String() string

String implements fmt.Stringer interface.

func (*ZCaseBuilder) When Uses

func (b *ZCaseBuilder) When(cond, then interface{}) *ZCaseBuilder

When adds "WHEN cond THEN then" to the builder.

func (*ZCaseBuilder) WriteExpression Uses

func (b *ZCaseBuilder) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface.

type ZDeleteBuilder Uses

type ZDeleteBuilder struct {
    Dialect qutil.Dialect
    Table   Table
    Wheres  ZAndExpr
}

ZDeleteBuilder implements a DELETE builder.

func Delete Uses

func Delete(table ...Table) *ZDeleteBuilder

Delete creates ZDeleteBuilder.

This is an example of how to use Delete.

Code:

user := q.T("user")
del := q.Delete(user).Where(q.Eq(user.C("id"), 1))
// del := q.Delete().From(user).Where(q.Eq(user.C("id"), 1)) // same
fmt.Println(del)

// Even in this case, the original name is used as a table and a column name
// because Insert, Delete and Update aren't supporting "AS" syntax.
u := q.T("user", "u")
fmt.Println(q.Delete(u).Where(q.Eq(u.C("id", "i"), 1)))

Output:

DELETE FROM "user" WHERE "id" = ? [1]
DELETE FROM "user" WHERE "id" = ? [1]

func (*ZDeleteBuilder) From Uses

func (b *ZDeleteBuilder) From(table Table) *ZDeleteBuilder

From sets a table to the FROM clause.

func (*ZDeleteBuilder) SetDialect Uses

func (b *ZDeleteBuilder) SetDialect(d qutil.Dialect) *ZDeleteBuilder

SetDialect sets a Dialect to the builder.

func (*ZDeleteBuilder) String Uses

func (b *ZDeleteBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZDeleteBuilder) ToPrepared Uses

func (b *ZDeleteBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZDeleteBuilder) ToSQL Uses

func (b *ZDeleteBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZDeleteBuilder) Where Uses

func (b *ZDeleteBuilder) Where(conds ...Expression) *ZDeleteBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

type ZInsertBuilder Uses

type ZInsertBuilder struct {
    Dialect   qutil.Dialect
    Beginning string
    Table     Table
    Sets      []struct {
        Name string
        Column
        Expression
    }
    Returnings []Column
}

ZInsertBuilder implements a INSERT builder.

func Insert Uses

func Insert(beginning ...string) *ZInsertBuilder

Insert creates ZInsertBuilder.

This is an example of how to use Insert.

Code:

user := q.T("user")
ins := q.Insert().Into(user).Set(user.C("name"), "hackme")
fmt.Println(ins)

Output:

INSERT INTO "user"("name") VALUES (?) [hackme]

func (*ZInsertBuilder) Into Uses

func (b *ZInsertBuilder) Into(table Table) *ZInsertBuilder

Into sets a table to the builder.

func (*ZInsertBuilder) Returning Uses

func (b *ZInsertBuilder) Returning(columns ...Column) *ZInsertBuilder

Returning appends a column to RETURNING clause. This feature is available for PostgreSQL only.

This is an example of how to use Insert.

Code:

user := q.T("user")
ins := q.Insert().Into(user).
    Set(user.C("name"), "hackme").
    Returning(user.C("id"), user.C("name", "n"))
fmt.Println("PostgreSQL", ins.SetDialect(q.PostgreSQL))
fmt.Println("MySQL", ins.SetDialect(q.MySQL))

Output:

PostgreSQL INSERT INTO "user"("name") VALUES ($1) RETURNING "id", "name" AS "n" [hackme]
MySQL INSERT INTO `user`(`name`) VALUES (?) [hackme]

func (*ZInsertBuilder) Set Uses

func (b *ZInsertBuilder) Set(c Column, v interface{}) *ZInsertBuilder

Set adds assignment expression to the builder.

func (*ZInsertBuilder) SetDialect Uses

func (b *ZInsertBuilder) SetDialect(d qutil.Dialect) *ZInsertBuilder

SetDialect sets a Dialect to the builder.

func (*ZInsertBuilder) String Uses

func (b *ZInsertBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZInsertBuilder) ToPrepared Uses

func (b *ZInsertBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZInsertBuilder) ToSQL Uses

func (b *ZInsertBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZInsertBuilder) Unset Uses

func (b *ZInsertBuilder) Unset(c Column) *ZInsertBuilder

Unset removes assignment expression from the builder.

type ZOrExpr Uses

type ZOrExpr []Expression

ZOrExpr represents OR Expression.

func (ZOrExpr) C Uses

func (e ZOrExpr) C(aliasName ...string) Column

C implements Expression interface method.

func (ZOrExpr) String Uses

func (e ZOrExpr) String() string

String implements fmt.Stringer interface method.

func (ZOrExpr) WriteExpression Uses

func (e ZOrExpr) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface method.

type ZSelectBuilder Uses

type ZSelectBuilder struct {
    Dialect   qutil.Dialect
    Beginning string
    Columns   []Column
    Tables    []Table
    Wheres    ZAndExpr
    Groups    []Expression
    Havings   ZAndExpr
    Orders    []struct {
        Expression
        Ascending bool
    }
    LimitCount  Expression
    StartOffset Expression
}

ZSelectBuilder implemenets a SELECT builder. This also implements Expression interface, so it can use in many place.

func Select Uses

func Select(beginning ...string) *ZSelectBuilder

Select creates ZSelectBuilder. If not needing an additional keyword around "SELECT", the argument can be omitted.

This is an example of how to use Select.

Code:

post, user := q.T("post"), q.T("user")
sel := q.Select().From(
    post.InnerJoin(
        user,
        q.Eq(post.C("user_id"), user.C("id")),
    ),
).Column(
    user.C("name"),
    post.C("message"),
).Where(
    q.Eq(post.C("id"), 100),
)
// You can also use `q.DefaultDialect = q.MySQL` instead of SetDialect.
fmt.Println(sel.SetDialect(q.MySQL).ToSQL())

Output:

SELECT `user`.`name`, `post`.`message` FROM `post` INNER JOIN `user` ON `post`.`user_id` = `user`.`id` WHERE `post`.`id` = ? [100]

This is an example of how to use the beginning argument.

Code:

user := q.T("user")
fmt.Println("Default:     ", q.Select().From(user))
fmt.Println("SQL_NO_CACHE:", q.Select("SELECT SQL_NO_CACHE").From(user))
fmt.Println("EXPLAIN:     ", q.Select("EXPLAIN SELECT").From(user))

Output:

Default:      SELECT * FROM "user" []
SQL_NO_CACHE: SELECT SQL_NO_CACHE * FROM "user" []
EXPLAIN:      EXPLAIN SELECT * FROM "user" []

func (*ZSelectBuilder) C Uses

func (b *ZSelectBuilder) C(aliasName ...string) Column

C implements Expression interface.

func (*ZSelectBuilder) Column Uses

func (b *ZSelectBuilder) Column(columns ...Column) *ZSelectBuilder

Column appends a column to the column list.

This is an example of how to use ZSelectBuilder.Column.

Code:

user := q.T("user")
fmt.Println("Default:  ", q.Select().From(user))
fmt.Println("Append:   ", q.Select().Column(user.C("id")).From(user))
fmt.Println("Aggregate:", q.Select().Column(q.CountAll().C("count")).From(user))

Output:

Default:   SELECT * FROM "user" []
Append:    SELECT "user"."id" FROM "user" []
Aggregate: SELECT COUNT(*) AS "count" FROM "user" []

func (*ZSelectBuilder) From Uses

func (b *ZSelectBuilder) From(tables ...Table) *ZSelectBuilder

From appends a table to the FROM clause.

This is an example of how to use ZSelectBuilder.From.

Code:

user := q.T("user")
fmt.Println("Simple: ", q.Select().From(user))
post := q.T("post")
fmt.Println("Complex:", q.Select().From(user, post).Where(
    q.Eq(user.C("id"), post.C("user_id")),
))
fmt.Println("Builder:", q.Select().From(q.Select().From(q.T("post")).T("p")))

Output:

Simple:  SELECT * FROM "user" []
Complex: SELECT * FROM "user", "post" WHERE "user"."id" = "post"."user_id" []
Builder: SELECT * FROM (SELECT * FROM "post") AS "p" []

func (*ZSelectBuilder) GroupBy Uses

func (b *ZSelectBuilder) GroupBy(e ...Expression) *ZSelectBuilder

GroupBy adds condition to the GROUP BY clause.

This is an example of how to use ZSelectBuilder.GroupBy.

Code:

user := q.T("user")
fmt.Println(
    q.Select().Column(q.CountAll().C("count")).From(user).GroupBy(user.C("age")),
)

Output:

SELECT COUNT(*) AS "count" FROM "user" GROUP BY "user"."age" []

func (*ZSelectBuilder) Having Uses

func (b *ZSelectBuilder) Having(conds ...Expression) *ZSelectBuilder

Having adds HAVING condition to the GROUP BY clause. More than one condition is connected by AND.

func (*ZSelectBuilder) Limit Uses

func (b *ZSelectBuilder) Limit(count interface{}) *ZSelectBuilder

Limit sets LIMIT clause to the builder.

This is an example of how to use ZSelectBuilder.Limit.

Code:

user := q.T("user")
fmt.Println("int:     ", q.Select().From(user).Limit(10))
fmt.Println("q.Unsafe:", q.Select().From(user).Limit(q.Unsafe(10, "*", 20)))

Output:

int:      SELECT * FROM "user" LIMIT ? [10]
q.Unsafe: SELECT * FROM "user" LIMIT 10*20 []

func (*ZSelectBuilder) Offset Uses

func (b *ZSelectBuilder) Offset(start interface{}) *ZSelectBuilder

Offset sets OFFSET clause to the builder.

This is an example of how to use ZSelectBuilder.Offset.

Code:

user := q.T("user")
fmt.Println("int:     ", q.Select().From(user).Limit(10).Offset(10))
fmt.Println("q.Unsafe:", q.Select().From(user).Limit(10).Offset(q.Unsafe(10, "*", 20)))

Output:

int:      SELECT * FROM "user" LIMIT ? OFFSET ? [10 10]
q.Unsafe: SELECT * FROM "user" LIMIT ? OFFSET 10*20 [10]

func (*ZSelectBuilder) OrderBy Uses

func (b *ZSelectBuilder) OrderBy(e Expression, asc bool) *ZSelectBuilder

OrderBy adds condition to the ORDER BY clause.

This is an example of how to use ZSelectBuilder.OrderBy.

Code:

user := q.T("user")
fmt.Println(
    "Single order:  ",
    q.Select().From(user).OrderBy(user.C("age"), true),
)
fmt.Println(
    "Multiple order:",
    q.Select().From(user).OrderBy(user.C("age"), true).OrderBy(
        q.CharLength(user.C("name")), false),
)

Output:

Single order:   SELECT * FROM "user" ORDER BY "user"."age" ASC []
Multiple order: SELECT * FROM "user" ORDER BY "user"."age" ASC, CHAR_LENGTH("user"."name") DESC []

func (*ZSelectBuilder) SetDialect Uses

func (b *ZSelectBuilder) SetDialect(d qutil.Dialect) *ZSelectBuilder

SetDialect sets a Dialect to the builder.

func (*ZSelectBuilder) String Uses

func (b *ZSelectBuilder) String() string

String implements fmt.Stringer interface.

func (*ZSelectBuilder) T Uses

func (b *ZSelectBuilder) T(aliasName string) Table

T creates Table from this builder.

func (*ZSelectBuilder) ToPrepared Uses

func (b *ZSelectBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and query arguments builder generator.

This is an example of how to use ZSelectBuilder.ToPrepared and V.

Code:

sql, gen := q.Select().From(q.T("user")).Where(
    q.Lte(q.C("id"), 100),
    q.Lte(q.C("age"), q.V(18, "findAge")),
).ToPrepared()

// // You can use by performing the following steps.
// stmt, err := db.Prepare(sql)
// if err != nil {
//   return err
// }
// defer stmt.Close()
//
// ab := gen()
// ab.Set("findAge", 24)
// stmt.Query(ab.Args...)

fmt.Println("SQL:", sql)

// build arguments
ab := gen()
fmt.Println("Default  Args:", ab.Args)
ab.Set("findAge", 24)
fmt.Println("Modified Args:", ab.Args)
// You can also rewrite other values by using an index,
// but there is a problem readability and weak to SQL change,
// so it isn't recommended.
// ab.Args[0] = 123
// ab.Args[1] = 24

Output:

SQL: SELECT * FROM "user" WHERE ("id" <= ?)AND("age" <= ?)
Default  Args: [100 18]
Modified Args: [100 24]

func (*ZSelectBuilder) ToSQL Uses

func (b *ZSelectBuilder) ToSQL() (string, []interface{})

ToSQL returns generated SQL and arguments.

This is an example of how to use ZSelectBuilder.ToSQL.

Code:

fmt.Println(q.Select().From(q.T("user")).Where(q.Lte(q.C("age"), 18)).ToSQL())

Output:

SELECT * FROM "user" WHERE "age" <= ? [18]

func (*ZSelectBuilder) Where Uses

func (b *ZSelectBuilder) Where(conds ...Expression) *ZSelectBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

This is an example of how to use ZSelectBuilder.Where.

Code:

user := q.T("user")
fmt.Println("Simple: ", q.Select().From(user).Where(q.Neq(user.C("id"), nil)))
post := q.T("post")
fmt.Println("Complex:", q.Select().From(user, post).Where(
    q.Neq(user.C("id"), nil),
    q.Gt(user.C("id"), 100),
))

Output:

Simple:  SELECT * FROM "user" WHERE "user"."id" IS NOT NULL []
Complex: SELECT * FROM "user", "post" WHERE ("user"."id" IS NOT NULL)AND("user"."id" > ?) [100]

func (*ZSelectBuilder) WriteExpression Uses

func (b *ZSelectBuilder) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface.

type ZUpdateBuilder Uses

type ZUpdateBuilder struct {
    Dialect   qutil.Dialect
    Beginning string
    Table     Table
    Sets      []struct {
        Name string
        Column
        Expression
    }
    Wheres ZAndExpr
}

ZUpdateBuilder implements a UPDATE builder.

func Update Uses

func Update(table Table, beginning ...string) *ZUpdateBuilder

Update creates ZUpdateBuilder.

This is an example of how to use Update.

Code:

upd := q.Update(q.T("user")).Set(q.C("name"), "hackme").Where(q.Eq(q.C("id"), 1))
fmt.Println(upd)
// Even in this case, the original name is used as a table and a column name
// because Insert, Delete and Update aren't supporting "AS" syntax.
u := q.T("user", "u")
fmt.Println(q.Update(u).Set(u.C("name"), "hackme").Where(q.Eq(u.C("id"), 1)))
// When overwriting in the same name, the last one is effective.
fmt.Println(q.Update(u).Set(u.C("name"), "hackyou").Set(u.C("name"), "hackme").Where(q.Eq(u.C("id"), 1)))

Output:

UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]
UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]
UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]

func (*ZUpdateBuilder) Set Uses

func (b *ZUpdateBuilder) Set(c Column, v interface{}) *ZUpdateBuilder

Set adds assignment expression to the SET clause.

func (*ZUpdateBuilder) SetDialect Uses

func (b *ZUpdateBuilder) SetDialect(d qutil.Dialect) *ZUpdateBuilder

SetDialect sets a Dialect to the builder.

func (*ZUpdateBuilder) String Uses

func (b *ZUpdateBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZUpdateBuilder) ToPrepared Uses

func (b *ZUpdateBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZUpdateBuilder) ToSQL Uses

func (b *ZUpdateBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZUpdateBuilder) Unset Uses

func (b *ZUpdateBuilder) Unset(c Column) *ZUpdateBuilder

Unset removes assignment expression from the SET clause.

func (*ZUpdateBuilder) Where Uses

func (b *ZUpdateBuilder) Where(conds ...Expression) *ZUpdateBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

Directories

PathSynopsis
qutil

Package q imports 3 packages (graph) and is imported by 1 packages. Updated 2016-07-31. Refresh now. Tools for package owners.