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?

Marquis

There are raw queries in gorm its the first thing I can think of gorm.io/docs/sql_builder.html. Also there is Union function take a look at the documentation pkg.go.dev/github.com/ale-dim/gorm#Union – Hickok

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

Borage

I tried to apply your answer but having problems to transfer this knowledge to the way i used gorm. Can you provide an answer that allows to use an ORM style (im not sure if thats the correct term here..). I mean if i don't want to use sql and do it like this: db.Where(&Foo{SortedByAttribute: valueToSort}).Find(&result). Thanks in advance – Corrie

@Corrie Did you read the first line in the answer? – Borage

@Corrie I don't know of any way to do unions in gorm without resorting to raw SQL. Maybe there is one, but I don't know about it so, I'm afraid, I can't help you. Perhaps you can open a new question here on SO that specifically asks for non-SQL gorm union code. Or you can go to gorm's github and ask there as well. – Borage

I check your links but they couldn't solve my problem. After researching I'm not even sure if it's possible to do it this way. But thanks to you, they helped in other ways :) I just posted a question on SO maybe someone else know's more about this. I will just leave a reference here in case this questions gets resolved and someone might approach the same problem. [link] (#69155331) – Corrie

Answer 2

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.

Saiga

Answer 3

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)

Henryhenryetta