Gorm: Union Queries

[bwiggs]

Is it possible to write union queries using the gorm sql builder? I couldn't find any docs or issues on the subject.

[kikihakiem]

Any update on this? Or is there any workaround? Thanks.

[tekkamanendless]

I've found that the easiest way is just to use .Joins("UNION ?", someHandle.QueryExpr()).

[hackervera]

I do db.Raw("? UNION ?", q1.QueryExpr(), q2.QueryExpr())

[nuczzz]

Raw(sql) + Scan() maybe useful, for example:

type RawInfo struct{
    OrderSn string
    Money   float64
}

var list []RawInfo 

if err := db.Raw(`  
    SELECT  
        order_sn,  
        money  
    FROM  
        (  
            (SELECT  
                order_sn,  
                sett_amt AS money,  
                create_time  
            FROM  
                table_a  
            WHERE  
                type='1'  
            )  
            UNION (  
                SELECT  
                    bill_no AS order_sn,  
                    amount AS money,  
                    create_time  
                FROM  
                    table_b  
                WHERE  
                    type='1'  
            )  
        ) AS temp  
    ORDER BY  
        create_time DESC  
    LIMIT  
        10`).Scan(&list).Error; err != nil {  
    //do something
}

[jchavannes]

Combining multiple of the suggestions above, the following worked well for me:

.Table("a").
.Joins("JOIN ((?) UNION DISTINCT (?)) bc ON (bc.a_id = a.id)",
    db.Select("a_id").Table("b").QueryExpr(),
    db.Select("a_id").Table("c").QueryExpr()).