Select [PostgreSQL MySQL]

API

For the full list of supported methods, see SelectQueryopen in new window.

1. db.NewSelect().
2. With("cte_name", subquery).

4. Model(&strct).
5. Model(&slice).

7. Column("col1", "col2"). // quotes column names
8. ColumnExpr("col1, col2"). // arbitrary unsafe expression
9. ColumnExpr("count(*)").
10. ColumnExpr("count(?)", bun.Ident("id")).
11. ColumnExpr("(?) AS alias", subquery).
12. ExcludeColumn("col1"). // all columns except col1
13. ExcludeColumn("*"). // exclude all columns

15. Table("table1", "table2"). // quotes table names
16. TableExpr("table1 AS t1"). // arbitrary unsafe expression
17. TableExpr("(?) AS alias", subquery).
18. ModelTableExpr("table1 AS t1"). // overrides model table name

20. Join("JOIN table2 AS t2 ON t2.id = t1.id").
21. Join("LEFT JOIN table2 AS t2").JoinOn("t2.id = t1.id").

23. WherePK(). // where using primary keys
24. Where("id = ?", 123).
25. Where("name LIKE ?", "my%").
26. Where("? = 123", bun.Ident("id")).
27. Where("id IN (?)", bun.In([]int64{1, 2, 3})).
28. Where("id IN (?)", subquery).
29. Where("FALSE").WhereOr("TRUE").
30. WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
31. return q.WhereOr("id = 1").
32. WhereOr("id = 2")
33. }).

35. Group("col1", "col2"). // quotes column names
36. GroupExpr("lower(col1)"). // arbitrary unsafe expression

38. Order("col1 ASC", "col2 DESC"). // quotes column names
39. OrderExpr("col1 ASC NULLS FIRST"). // arbitrary unsafe expression

41. Having("column_name > ?", 123).

43. Limit(100).
44. Offset(100).

46. For("UPDATE").
47. For("SHARE").

49. Scan(ctx)

Example

To select into a struct, define a model and use SelectQueryopen in new window:

1. book := new(Book)
2. err := db.NewSelect().Model(book).Where("id = ?", 123).Scan(ctx)

Count rows

Bun provides Countopen in new window helper to generate count(*) queries:

1. count, err := db.NewSelect().Model((*User)(nil)).Count(ctx)

Because selecting and counting rows is a common operation, Bun also provides ScanAndCountopen in new window:

1. var users []User
2. count, err := db.NewSelect().Model(&users).Limit(20).ScanAndCount(ctx)
3. if err != nil {
4. panic(err)
5. }
6. fmt.Println(users, count)

EXISTS

You can also use Existsopen in new window helper to use the corresponding EXISTS SQL operator:

1. exists, err := db.NewSelect().Model((*User)(nil)).Where("name LIKE '%foo%'").Exists(ctx)
2. if err != nil {
3. panic(err)
4. }
5. if !exists {
6. fmt.Println("such user does not exist")
7. }

1. SELECT EXISTS (SELECT * FROM users WHERE name LIKE '%foo%')

Joins

To select a book and manually join the book author:

1. book := new(Book)
2. err := db.NewSelect().
3. Model(book).
4. ColumnExpr("book.*").
5. ColumnExpr("a.id AS author__id, a.name AS author__name").
6. Join("JOIN authors AS a ON a.id = book.author_id").
7. OrderExpr("book.id ASC").
8. Limit(1).
9. Scan(ctx)

1. SELECT book.*, a.id AS author__id, a.name AS author__name
2. FROM books
3. JOIN authors AS a ON a.id = book.author_id
4. ORDER BY book.id ASC
5. LIMIT 1

To generate complex joins, use JoinOn:

1. q = q.
2. Join("JOIN authors AS a").
3. JoinOn("a.id = book.author_id").
4. JoinOn("a.deleted_at IS NULL")

1. JOIN authors AS a ON a.id = book.author_id AND a.deleted_at IS NULL

Subqueries

You can use Bun queries (including INSERT, UPDATE, and DELETE queries) as a subquery:

1. subq := db.NewSelect().Model((*Book)(nil)).Where("author_id = ?", 1)

3. err := db.NewSelect().Model().TableExpr("(?) AS book", subq).Scan(ctx, &books)

1. SELECT * FROM (
2. SELECT "book"."id", "book"."title", "book"."text"
3. FROM "books" AS "book" WHERE (author_id = 1)
4. ) AS book