表格式及关系

package main

import (
  "fmt"
  "github.com/aacanakin/qb"
)

func main() {
  db, _ := qb.New("mysql", "root:@tcp(localhost:3306)/qb_test?charset=utf8")
  defer db.Close()

  users := qb.Table(
    "user",
    qb.Column("id", qb.Varchar().Size(36)),
    qb.Column("email", qb.Varchar().Unique().NotNull()),
    qb.Column("password", qb.Varchar().NotNull()),
    qb.Column("name", qb.Varchar().NotNull()),
    qb.PrimaryKey("id"),
  )

  sessions := qb.Table(
    "session",
    qb.Column("id", qb.Varchar().Size(36)),
    qb.Column("user_id", qb.Varchar().Size(36)),
    qb.Column("auth_token", qb.Varchar().Size(36)),
    qb.Column("created_at", qb.Timestamp().NotNull()),
    qb.Column("expires_at", qb.Timestamp().Null()),
    qb.ForeignKey().Ref("user_id", "users", "id"),
  )

  fmt.Println(users.Create(db.Dialect()))
  fmt.Println(sessions.Create(db.Dialect()))
}

简单查询

sql: SELECT id, email FROM user WHERE user.name = ?;

sel := users.
    Select(users.C("id"), users.C("email")).
    Where(users.C("name").Eq("Al Pacino")).
    Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

AND 和 OR 语句

• AND
  sql: SELECT id, email FROM user WHERE (user.name = ? AND user.email != ?);

sel := users.
    Select(users.C("id"), users.C("email")).
    Where(
      qb.And(
        users.C("name").Eq("Al Pacino"),
        users.C("email").NotEq("robert@downey.com"),
      )).
    Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

• OR
  sql: SELECT id, email FROM user WHERE (user.name = ? OR user.email != ?);

sel := users.
    Select(users.C("id"), users.C("email")).
    Where(
      qb.Or(
        users.C("name").Eq("Al Pacino"),
        users.C("email").NotEq("robert@downey.com"),
      )).
    Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

• AND 和 OR 一起使用
  sql: SELECT id, email FROM user WHERE (user.name = ? AND (user.email = ? OR user.email = ?));

sel := users.
    Select(users.C("id"), users.C("email")).
    Where(
      qb.And(
        users.C("name").Eq("Al Pacino"),
        qb.Or(
          users.C("email").Eq("al@pacino.com"),
          users.C("email").Eq("pacino@al.com"),
        ),
      )).
    Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

使用连接 JOIN

sql:

SELECT session.id, session.user_id, session.created_at, session.expires_at
FROM session INNER JOIN user 
ON session.user_id = user.id
WHERE session.user_id = ?;

sel := qb.
    Select(
      sessions.C("id"),
      sessions.C("user_id"),
      sessions.C("created_at"),
      sessions.C("expires_at"),
    ).
    From(sessions).
    InnerJoin(users, sessions.C("user_id"), users.C("id")).
    Where(sessions.C("user_id").Eq("3af82cdc-4d21-473b-a175-cbc3f9119eda")).
    Build(db.Dialect())

交叉连接(笛卡尔积)不需要列连接。因此，它只有表参数。