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 ValuesQueryCommon table expressions - 图1open 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 WithOrderCommon table expressions - 图2open 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