How to do union of multiple tables with where clause and order by in GORM (golang)

user_2758467 0
Viewed 129

I'm trying to do union of multiple tables with selected columns and run where clause and order by clause on the resultset. How do I write this in GORM (Golang)

I tried the following snippet, but didn't run the where clause and order by clause in the DB query:

var union []map[string]interface{}
database.CONNECTION.Raw("? UNION ?",
    database.CONNECTION.Select(ContentAttributes).Model(&model1{}),
    database.CONNECTION.Select(ContentAttributes).Model(&model2{}),
).Where("id > ?", 1).Order("Name").Scan(&union)

N.B. ContentAttributes is a slice of string which contains the attributes I want to select.

It's running the following query:

SELECT "id","name","created_at","updated_at" FROM "model1" WHERE "model1"."deleted_at" IS NULL UNION SELECT "id","name","created_at","updated_at" FROM "model2" WHERE "model2"."deleted_at" IS NULL

I expected this to run the where condition and the order by clause on the union resultset. But it just did union and collected the results in the union variable. Please suggest a way to do this.

go-gorm go where-clause union sql-order-by
1 Answers

Not sure if this is the cleanest way to do it but it works.

var db = database.CONNECTION
var union []map[string]interface{}
var raw = "SELECT * (? UNION ?) union WHERE union.id > ? ORDER BY union.name"

db.Raw(raw,
    db.Select("*").Model(&model1{}),
    db.Select("*").Model(&model2{}),
    1
).Scan(&union)
user_89958720
Related