Common table expressions [PostgreSQL MySQL]

With

Most Bun queries support CTEs via With method:

1. q1 := db.NewSelect()
2. q2 := db.NewSelect()

4. q := db.NewInsert().
5. With("q1", q1).
6. With("q2", q2).
7. Table("q1", "q2")

For example, you can use CTEs to bulk-delete rows that match some predicates:

1. const limit = 1000

3. for {
4. subq := db.NewSelect().
5. Model((*Comment)(nil)).
6. Where("created_at < now() - interval '90 day'").
7. Limit(limit)

9. res, err := db.NewDelete().
10. With("todo", subq).
11. Model((*Comment)(nil)).
12. Table("todo").
13. Where("comment.id = todo.id").
14. Exec(ctx)
15. if err != nil {
16. panic(err)
17. }

19. num, err := res.RowsAffected()
20. if err != nil {
21. panic(err)
22. }
23. if num < limit {
24. break
25. }
26. }

1. WITH todo AS (
2. SELECT * FROM comments
3. WHERE created_at < now() - interval '90 day'
4. LIMIT 1000
5. )
6. DELETE FROM comments AS comment USING todo
7. WHERE comment.id = todo.id

Or copy data between tables:

1. src := db.NewSelect().Model((*Comment)(nil))

3. res, err := db.NewInsert().
4. With("src", src).
5. Table("comments_backup", "src").
6. Exec(ctx)

1. WITH src AS (SELECT * FROM comments)
2. INSERT INTO comments_backups SELECT * FROM src

VALUES

Bun also provides ValuesQueryopen in new window to help building CTEs:

1. values := db.NewValues([]*Book{book1, book2})

3. res, err := db.NewUpdate().
4. With("_data", values).
5. Model((*Book)(nil)).
6. Table("_data").
7. Set("title = _data.title").
8. Set("text = _data.text").
9. Where("book.id = _data.id").
10. Exec(ctx)

1. WITH _data (id, title, text) AS (VALUES (1, 'title1', 'text1'), (2, 'title2', 'text2'))
2. UPDATE books AS book
3. SET title = _data.title, text = _data.text
4. FROM _data
5. WHERE book.id = _data.id

You can also use WithOrderopen in new window to include row rank in values:

1. users := []User{
2. {ID: 1, "one@my.com"},
3. {ID: 2, "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