How to use mysql Union All on GORM?

When developing with GORM, you may occasionally need to execute complex SQL queries such as UNION ALL. GORM is primarily an ORM (Object-Relational Mapping) tool designed to simplify database CRUD (Create, Read, Update, Delete) operations. While GORM is sufficiently powerful for most everyday development tasks, specific SQL operations like UNION ALL often require direct use of native SQL statements.

Below are the steps and examples for executing UNION ALL queries with GORM:

Step 1: Construct Native SQL Queries

First, construct the appropriate SQL query based on your requirements. For example, suppose you have two tables users and admins, and you need to combine data from both.

sql
SELECT name, email FROM users
UNION ALL
SELECT name, email FROM admins

Step 2: Execute Native SQL with GORM

After constructing the appropriate SQL statement, use GORM's Raw method to execute the query. Here is how to implement this in Go code:

go
package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Result struct {
    Name  string
    Email string
}

func main() {
    // Connect to the database; replace this with your connection string
    dsn := "your_user:your_password@tcp(your_host:your_port)/your_db?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // Execute UNION ALL query
    var results []Result
    db.Raw("SELECT name, email FROM users UNION ALL SELECT name, email FROM admins").Scan(&results)

    // Output results
    for _, result := range results {
        fmt.Printf("Name: %s, Email: %s\n", result.Name, result.Email)
    }
}

Notes

1. Ensure SQL statement security when using native SQL to prevent SQL injection vulnerabilities.
2. When using UNION ALL, verify that all columns involved in the UNION are compatible in data type.

Through this example, you can see that while GORM provides convenient ORM features, for specific operations like UNION ALL, native SQL statements are often more direct and effective. This approach maintains code clarity while fully leveraging SQL's capabilities, especially for complex queries.