Golang Where PostgreSQL MySQL
基础
您可以在 Where 中使用任意不安全的表达式
q = q.Where("column LIKE 'hello%'")
要安全地构建动态 WHERE 子句，请使用 占位符 和 bun.Ident
q = q.Where("? LIKE ?", bun.Ident("mycolumn"), "hello%")
QueryBuilder
Bun 提供 QueryBuilder 接口，它支持构建查询所需的常用方法，例如
func addWhere(q bun.QueryBuilder) bun.QueryBuilder {
return q.Where("id = ?", 123)
}
qb := db.NewSelect().QueryBuilder()
addWhere(qb)
qb := db.NewUpdate().QueryBuilder()
addWhere(qb)
qb := db.NewDelete().QueryBuilder()
addWhere(qb)
// Alternatively.
db.NewSelect().ApplyQueryBuilder(addWhere)
db.NewUpdate().ApplyQueryBuilder(addWhere)
db.NewDelete().ApplyQueryBuilder(addWhere)
QueryBuilder 和 ApplyQueryBuilder 函数都返回一个 QueryBuilder 接口类型结构体。构建完查询后，您需要检索原始 Query 结构体，以便能够调用 Scan 或 Exec 函数。为此，您必须解包查询构建器结构体，然后将其强制转换为所需的类型，如下所示
qb := db.NewSelect().QueryBuilder().Where("id = ?", 123)
selectQuery = qb.Unwrap().(*bun.SelectQuery)
WHERE IN
如果您已经有一个 id 列表，请使用 bun.In
q = q.Where("user_id IN (?)", bun.In([]int64{1, 2, 3}))
您也可以使用子查询
subq := db.NewSelect().Model((*User)(nil)).Column("id").Where("active")
q = q.Where("user_id IN (?)", subq)
WherePK
WherePK 允许使用模型主键自动生成 WHERE 子句
users := []User{
{ID: 1},
{ID: 2},
{ID: 3},
}
err := db.NewSelect().Model(&users).WherePK().Scan(ctx)
SELECT * FROM users WHERE id IN (1, 2, 3)
WherePK 还接受一个列列表，可以用来代替主键来标识行
users := []User{
{Email: "one@my.com"},
{Email: "two@my.com"},
{Email: "three@my.com"},
}
err := db.NewSelect().Model(&users).WherePK("email").Scan(ctx)
SELECT * FROM users WHERE email IN ('one@my.com', 'two@my.com', 'three@my.com')
WHERE VALUES
您可以使用 CTE 和 VALUES 构建复杂的查询
users := []User{
{ID: 1, Email: "one@my.com"},
{ID: 2, Email: "two@my.com"},
}
err := db.NewSelect().
With("data", db.NewValues(&users).WithOrder()).
Model(&users).
Where("user.id = data.id").
OrderExpr("data._order").
Scan(ctx)
WITH "data" ("id", "email", _order) AS (
VALUES
(42::BIGINT, 'one@my.com'::VARCHAR, 0),
(43::BIGINT, 'two@my.com'::VARCHAR, 1)
)
SELECT "user"."id", "user"."email"
FROM "users" AS "user"
WHERE (user.id = data.id)
ORDER BY data._order
分组
您可以使用 WhereOr 将条件与逻辑 OR 连接起来
q = q.Where("id = 1").WhereOr("id = 2").WhereOr("id = 3")
要将条件用括号分组，请使用 WhereGroup
q = q.
WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
return q.Where("id = 1").WhereOr("id = 2").WhereOr("id = 3")
}).
WhereGroup(" AND NOT ", func(q *bun.SelectQuery) *bun.SelectQuery {
return q.Where("active").WhereOr("archived")
})
WHERE (id = 1 OR id = 2 OR id = 3) AND NOT (active OR archived)
