Select [PostgreSQL MySQL]

    Example

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

    2. err := db.NewSelect().Model(book).Where("id = ?", 123).Scan(ctx)
    1. count, err := db.NewSelect().Model((*User)(nil)).Count(ctx)

    Because selecting and counting rows is a common operation, Bun also provides :

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

    EXISTS

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

    To select a book and manually join the book author:

    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. FROM books
    2. JOIN authors AS a ON a.id = book.author_id
    4. LIMIT 1
    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"
    4. ) AS book