How to use mysql Union All on GORM?

Question

I'm working with complicated structure database, and after update we start using GORM so I need to transform this script using GORM.

query := `
  SELECT * FROM foo
  UNION ALL
  SELECT * FROM bar WHERE id=1`
rows, err := db.Query(query)

What is the best way to do it?

Answer 1

Note that gorm doesn't support UNION directly, you need to use db.Raw to do UNIONs:

db.Raw("? UNION ?",
    db.Select("*").Model(&Foo{}),
    db.Select("*").Model(&Bar{}),
).Scan(&union)

the above will produce something like the following:

SELECT * FROM "foos"
WHERE "foos"."deleted_at" IS NULL
UNION
SELECT * FROM "bars"
WHERE "bars"."deleted_at" IS NULL