"CASE WHEN" subquery as value in InsertQuery

[pof3]

Hi!
After looking at the InsertQuery and SelectQuery API, I've been struggling to get the following query to work:

INSERT INTO "comments" (
  "id", "media_id", "thread_id", "user_id", "body", "is_thread_author", "created_at", "updated_at", "deleted_at"
) VALUES (
  DEFAULT,
  '?',
  '?',
  '?',
  '?',
  (CASE WHEN (SELECT "user_id" FROM "threads" WHERE "id" = '?') = '?' THEN true ELSE false END),
  DEFAULT,
  DEFAULT,
  DEFAULT
)

As you can see, basically I want to determine whether the author of the comment is the same who authored the thread, and set the "is_thread_author" column accordingly. I've tried creating a SelectQuery , and append it to the main InsertQuery via the With method, also with the Value and Column methods, but I haven't been able to generate the proper SQL query through bun. Any help to figure out how to inject that statement will be greatly appreciated.

[jhhom]

This is one way I figured out how to do it using the InsertQuery and Value method:

func main() {
	// ...
	c := &Comment{
		ThreadID: 1,
		UserID:   1,
	}
	_, err := db.NewInsert().Model(c).
		Value("is_thread_author", `
	(CASE WHEN (SELECT user_id FROM threads WHERE id = ?) = ? THEN TRUE ELSE FALSE END)`, c.ThreadID, c.UserID).
		Exec(ctx)
	if err != nil {
		panic(fmt.Errorf("error, %w", err))
	}
}

type Comment struct {
	bun.BaseModel `bun:"table:comments"`

	ThreadID       int64 `bun:"thread_id"`
	UserID         int64 `bun:"user_id"`
	IsThreadAuthor bool  `bun:"is_thread_author"`
	// ... other columns omitted
}

It will generate an SQL query like this:

INSERT INTO "comments" ("thread_id", "user_id", "is_thread_author") VALUES (1, 1,
	(CASE WHEN (SELECT user_id FROM threads WHERE id = 1) = 1 THEN TRUE ELSE FALSE END)) RETURNING "is_thread_author"

Is this what you're looking for?

[pof3]

Thanks! It actually worked. The only minor issue was that I had to wrap "user_id" in the subquery with "". I really appreciate it!