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 QueryBuilderopen 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)