How to alias a relation in a JOIN in GORM?

In GORM database operations, if you need to use aliases to reference tables within JOIN operations, you can achieve this through several methods:

Method 1: Using Native SQL Fragments

Suppose we have two tables: the users table and the profiles table, connected via user_id. We want to assign the alias p to the profiles table in the JOIN operation.

go
type User struct {
    ID   uint
    Name string
}

type Profile struct {
    ID     uint
    UserID uint
    Bio    string
}

// Querying
result := db.Table("users").Select("users.name, p.bio").
    Joins("left join profiles as p on p.user_id = users.id").
    Where("users.name = ?", "John").
    Scan(&resultData)

In this example, we add a native SQL JOIN fragment using the Joins() method, where the profiles table is aliased as p.

Method 2: Using gorm.Expr

If you require greater flexibility in constructing SQL statements within GORM's chained operations, you can use gorm.Expr to build the necessary SQL expression.

go
db.Model(&User{}).
    Joins("LEFT JOIN ? AS p ON p.user_id = users.id", gorm.Expr("profiles")).
    Where("users.name = ?", "John").
    Scan(&resultData)

Here, gorm.Expr("profiles") generates the required expression to correctly specify the table name, and AS assigns the alias p in the JOIN clause.

Method 3: Struct Tags

If your application scenario permits, you can define table aliases using struct tags in GORM models, though this approach is not suitable for dynamic aliases and is best reserved for fixed aliases.

go
type Profile struct {
    gorm.Model
    UserID uint
    Bio    string
    // Specify table name as `p`
    _      struct{} `gorm:"table:p"`
}

This method is relatively inflexible because it hardcodes the alias into the model definition, but it may be useful in specific scenarios where fixed aliases are required.

In summary, based on your specific requirements, choose the most appropriate method to alias tables in JOIN operations. In practice, Methods 1 and 2 are more commonly used due to their flexibility and control over SQL statement composition.