Where [PostgreSQL MySQL]

Basics

You can use arbitrary unsafe expressions in Where:

  1. q = q.Where("column LIKE 'hello%'")

To safely build dynamic WHERE clauses, use placeholders and bun.Ident:

  1. q = q.Where("? LIKE ?", bun.Ident("mycolumn"), "hello%")

QueryBuilder

Bun provides QueryBuilderWHERE - 图1open in new window interface which supports common methods required to build queries, for example:

  1. func addWhere(q bun.QueryBuilder) bun.QueryBuilder {
  2.     return q.Where("id = ?", 123)
  3. }
  5. qb := db.NewSelect().QueryBuilder()
  6. addWhere(qb)
  8. qb := db.NewUpdate().QueryBuilder()
  9. addWhere(qb)
  11. qb := db.NewDelete().QueryBuilder()
  12. addWhere(qb)
  14. // Alternatively.
  16. db.NewSelect().ApplyQueryBuilder(addWhere)
  17. db.NewUpdate().ApplyQueryBuilder(addWhere)
  18. db.NewDelete().ApplyQueryBuilder(addWhere)

WHERE IN

If you already have a list of ids, use bun.In:

  1. q = q.Where("user_id IN (?)", bun.In([]int64{1, 2, 3}))

You can also use subqueries:

  1. subq := db.NewSelect().Model((*User)(nil)).Column("id").Where("active")
  3. q = q.Where("user_id IN (?)", subq)

WherePK

WherePK allows to auto-generate a WHERE clause using model primary keys:

  1. users := []User{
  2.     {ID: 1},
  3.     {ID: 2},
  4.     {ID: 3},
  5. }
  6. err := db.NewSelect().Model(&users).WherePK().Scan(ctx)
  1. SELECT * FROM users WHERE id IN (1, 2, 3)

WherePK also accepts a list of columns that can be used instead of primary keys to indentify rows:

  1. users := []User{
  2.     {Email: "one@my.com"},
  3.     {Email: "two@my.com"},
  4.     {Email: "three@my.com"},
  5. }
  6. err := db.NewSelect().Model(&users).WherePK("email").Scan(ctx)
  1. SELECT * FROM users WHERE email IN ('one@my.com', 'two@my.com', 'three@my.com')

WHERE VALUES

You can build complex queries using CTE and VALUES:

  1. users := []User{
  2.     {ID: 1, Email: "one@my.com"},
  3.     {ID: 2, Email: "two@my.com"},
  4. }
  6. err := db.NewSelect().
  7.     With("data", db.NewValues(&users).WithOrder()).
  8.     Model(&users).
  9.     Where("user.id = data.id").
  10.     OrderExpr("data._order").
  11.     Scan(ctx)
  1. WITH "data" ("id", "email", _order) AS (
  2.   VALUES
  3.     (42::BIGINT, 'one@my.com'::VARCHAR, 0),
  4.     (43::BIGINT, 'two@my.com'::VARCHAR, 1)
  5. )
  6. SELECT "user"."id", "user"."email"
  7. FROM "users" AS "user"
  8. WHERE (user.id = data.id)
  9. ORDER BY data._order

Grouping

You can use WhereOr to join conditions with logical OR:

  1. q = q.Where("id = 1").WhereOr("id = 2").WhereOr("id = 3")

To group conditions with parentheses, use WhereGroup:

  1. q = q.
  2.     WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
  3.         return q.Where("id = 1").WhereOr("id = 2").WhereOr("id = 3")
  4.     }).
  5.     WhereGroup(" AND NOT ", func(q *bun.SelectQuery) *bun.SelectQuery {
  6.         return q.Where("active").WhereOr("archived")
  7.     })
  1. WHERE (id = 1 OR id = 2 OR id = 3) AND NOT (active OR archived)