一、复用Join逻辑，避免Preload重复编写条件 #

Gorm的Preload默认会执行独立查询，但可以通过Preload的回调函数自定义查询逻辑——把你复杂的Join逻辑抽成可复用的函数，Preload时直接调用就行，不用重复写一堆代码。

步骤1：抽离共用的Join逻辑 #

先把重复用到的Join条件封装成函数：

func buildPolicyJoin(db *gorm.DB, userID string) *gorm.DB {
    return db.Joins("LEFT JOIN map_policy_identities ON map_policy_identities.assign_type = 'user' AND map_policy_identities.assign_id = ?", userID).
        Joins("LEFT JOIN policy_policy ON map_policy_identities.pol_id = policy_policy.uid AND policy_policy.is_enabled = true").
        Joins("LEFT JOIN map_policy_apps ON map_policy_identities.pol_id = map_policy_apps.pol_id")
}

步骤2：在Preload中复用该函数 #

修改原查询，在Preload里传入自定义逻辑，让Preload沿用你的Join条件：

var assets []*Assetinfo 

err := Db.Table("application_application apps").
    Select("Distinct apps.*, apps.avatar as icon, profile_asset.address as ip, apps.is_health_check as is_need_request").
    Joins("LEFT JOIN map_policy_identities ON map_policy_identities.assign_type = 'user' AND map_policy_identities.assign_id = ?", userid).
    Joins("LEFT JOIN policy_policy ON map_policy_identities.pol_id = policy_policy.uid AND policy_policy.is_enabled = true").
    Joins("LEFT JOIN map_policy_apps ON map_policy_identities.pol_id = map_policy_apps.pol_id").
    Joins("LEFT JOIN map_category_apps ON map_category_apps.category_id = map_policy_apps.app_id").
    Joins("LEFT JOIN map_app_assets ON apps.uid = map_app_assets.app_id").
    Joins("LEFT JOIN profile_asset ON profile_asset.uid = map_app_assets.asset_id").
    Where("map_policy_apps.app_id = apps.uid OR apps.uid = map_category_apps.app_id").
    Where("apps.is_enabled = true AND apps.is_visibled = true").
    // 自定义Preload逻辑，复用封装好的Join函数
    Preload("MapPolicyApps", func(db *gorm.DB) *gorm.DB {
        return buildPolicyJoin(db, userid).Where("map_policy_apps.app_id = apps.uid OR apps.uid = map_category_apps.app_id")
    }).
    Preload("MapPolicyApps.PolicyPolicy", func(db *gorm.DB) *gorm.DB {
        return buildPolicyJoin(db, userid).Where("policy_policy.is_enabled = true")
    }).
    Preload("MapPolicyApps.PolicyPolicy.MapPolicyProfiles.ProfileAddr").
    Preload("MapPolicyApps.PolicyPolicy.MapPolicyProfiles.ProfileTimerange").
    Preload("MapPolicyApps.PolicyPolicy.MapPolicyProfiles.ProfilePosturecheck").
    Find(&assets).Error

二、无需Preload，仅用Join加载所有嵌套结构体 #

完全可以做到，核心是通过Select指定字段的映射关系，让Gorm直接把查询结果映射到嵌套结构体里，只执行一次SQL查询。

注意事项 #

• 要确保结构体字段和数据库列名对应，或者用gorm:"column:xxx"标签明确指定
• 对于切片类型的嵌套结构体（比如[]MapPolicyApps），Gorm会自动根据父级主键分组聚合，要保证查询结果的关联数据能正确匹配

示例代码 #

修改查询语句，在Select里明确所有嵌套字段的映射：

var assets []*Assetinfo 

err := Db.Table("application_application apps").
    Select(`
        apps.uid as uid,
        apps.avatar as icon,
        profile_asset.address as ip,
        apps.is_health_check as is_need_request,
        // MapPolicyApps字段映射，用双下划线分隔嵌套层级
        map_policy_apps.id as map_policy_apps__id,
        map_policy_apps.pol_id as map_policy_apps__pol_id,
        map_policy_apps.app_type as map_policy_apps__app_type,
        map_policy_apps.app_id as map_policy_apps__app_id,
        map_policy_apps.created_at as map_policy_apps__created_at,
        // PolicyPolicy字段映射
        policy_policy.uid as map_policy_apps__policy_policy__uid,
        policy_policy.is_enabled as map_policy_apps__policy_policy__is_enabled,
        // MapPolicyProfiles及关联字段映射，按需补充
        map_policy_profiles.id as map_policy_apps__policy_policy__map_policy_profiles__id,
        profile_addr.address as map_policy_apps__policy_policy__map_policy_profiles__profile_addr__address
    `).
    Joins("LEFT JOIN map_policy_identities ON map_policy_identities.assign_type = 'user' AND map_policy_identities.assign_id = ?", userid).
    Joins("LEFT JOIN policy_policy ON map_policy_identities.pol_id = policy_policy.uid AND policy_policy.is_enabled = true").
    Joins("LEFT JOIN map_policy_apps ON map_policy_identities.pol_id = map_policy_apps.pol_id").
    Joins("LEFT JOIN map_category_apps ON map_category_apps.category_id = map_policy_apps.app_id").
    Joins("LEFT JOIN map_app_assets ON apps.uid = map_app_assets.app_id").
    Joins("LEFT JOIN profile_asset ON profile_asset.uid = map_app_assets.asset_id").
    // 补充MapPolicyProfiles及关联表的Join
    Joins("LEFT JOIN map_policy_profiles ON map_policy_profiles.pol_id = policy_policy.uid").
    Joins("LEFT JOIN profile_addr ON profile_addr.uid = map_policy_profiles.addr_id").
    Joins("LEFT JOIN profile_timerange ON profile_timerange.uid = map_policy_profiles.timerange_id").
    Joins("LEFT JOIN profile_posturecheck ON profile_posturecheck.uid = map_policy_profiles.posturecheck_id").
    Where("map_policy_apps.app_id = apps.uid OR apps.uid = map_category_apps.app_id").
    Where("apps.is_enabled = true AND apps.is_visibled = true").
    Scan(&assets).Error

关键说明 #

• 字段别名用双下划线__区分嵌套层级，比如map_policy_apps__policy_policy__uid会自动映射到Assetinfo.MapPolicyApps[].PolicyPolicy.Uid
• 这种方式只会执行一次SQL，避免了Preload带来的N+1查询问题，但需要手动维护所有字段的映射关系，适合查询逻辑固定的场景

总结 #

• 想保留Preload灵活性的话，用自定义Preload回调+复用Join函数，避免重复写复杂逻辑
• 追求性能、想减少查询次数的话，用Join+自定义Select字段映射，一次性加载所有嵌套数据

内容的提问来源于stack exchange，提问作者Bad Boy