CODEWITHSUNDEEP
mysqlgogo-gorm
Muslihudin
Muslihudin

Reputation: 368

How to use mysql Union All on GORM?

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?

Upvotes: 10

Views: 10674

Answers (3)

Chris Cheng
Chris Cheng

Reputation: 101

thanks to @mkopriva db.Raw will do the job. although the code provided might not work with legacy gorm(github.com/jinzhu/gorm), you need to convert *gorm.DB to gorm.SqlExpr in raw clause

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

Upvotes: 0

alz2
alz2

Reputation: 206

From this github issue, there's a workaround where if you don't want to use db.Raw, you can actually use Joins. Something like the following should work.

db.Table("foo").Joins("UNION ?", db.Table("bar").Where("id = ?", 1)).Scan(&results)

Not pretty, but this'll let you use other chain-able methods since you can't method chain with Raw.

Upvotes: 0

mkopriva
mkopriva

Reputation: 38213

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

Upvotes: 21

Related Questions