How to preload in gorm many to many with the condition in join table

In database operations with GORM, preloading is a commonly used technique, especially when handling many-to-many relationships. Preloading refers to loading related table data simultaneously with the main table query, which helps avoid subsequent N+1 query issues.

Suppose we have a many-to-many relationship, such as User and Role, where they are linked through a join table, which we can call user_roles. Now, if we need to retrieve only users with specific roles, we can achieve this by combining GORM's Preload method with a subquery.

Below is a specific example demonstrating how to preload only the roles of users with the "Admin" role:

go
package main

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

type User struct {
	horm.Model
	Name  string
	Roles []Role `gorm:"many2many:user_roles;"`
}

type Role struct {
	horm.Model
	Name string
}

func main() {
	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}

	// Auto-migrate
	db.AutoMigrate(&User{}, &Role{})

	// Create sample data
	adminRole := Role{Name: "Admin"}
	userRole := Role{Name: "User"}
	db.Create(&adminRole)
	db.Create(&userRole)

	user1 := User{Name: "Alice", Roles: []Role{adminRole, userRole}}
	user2 := User{Name: "Bob", Roles: []Role{userRole}}

	db.Create(&user1)
	db.Create(&user2)

	// Preload roles for users with "Admin" role
	var users []User
	db.Preload("Roles", "id IN (SELECT role_id FROM user_roles JOIN roles ON roles.id = user_roles.role_id WHERE roles.name = ?)", "Admin").Find(&users)

	for _, user := range users {
		fmt.Printf("User: %s\n", user.Name)
		for _, role := range user.Roles {
			fmt.Printf("Role: %s\n", role.Name)
		}
	}
}

In this example:

1. We define two models User and Role, and specify the many-to-many relationship using the gorm:"many2many:user_roles;" tag.
2. In the preloading section, we use the Preload method. We specify the preloading conditions by using a subquery: only preload roles named "Admin".
3. The subquery "id IN (SELECT role_id FROM user_roles JOIN roles ON roles.id = user_roles.role_id WHERE roles.name = ?)" is used to filter role IDs where the role name is "Admin".

Thus, when retrieving user information, only the roles with "Admin" will be loaded. This significantly reduces unnecessary data loading and improves query efficiency.