Get nested object with one query

I have the following model

type Instance struct {
	gorm.Model
	Name               string `gorm:"unique;"`
	UserID             uint
	GroupID            uint
	StackID            uint
	RequiredParameters []InstanceRequiredParameter `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"requiredParameters,omitempty"`
	OptionalParameters []InstanceOptionalParameter `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"optionalParameters,omitempty"`
	DeployLog          string                      `gorm:"type:text"`
}

type InstanceRequiredParameter struct {
	gorm.Model
	InstanceID               uint                   `gorm:"index:idx_instance_required_parameter,unique"`
	StackRequiredParameterID uint                   `gorm:"index:idx_instance_required_parameter,unique"`
	StackRequiredParameter   StackRequiredParameter `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
	Value                    string
}

type InstanceOptionalParameter struct {
	gorm.Model
	InstanceID               uint                   `gorm:"index:idx_instance_optional_parameter,unique"`
	StackOptionalParameterID uint                   `gorm:"index:idx_instance_optional_parameter,unique"`
	StackOptionalParameter   StackOptionalParameter `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
	Value                    string
}

type StackRequiredParameter struct {
	gorm.Model
	StackID uint   `gorm:"index:idx_name_required_parameter,unique"`
	Name    string `gorm:"index:idx_name_required_parameter,unique"`
}

type StackOptionalParameter struct {
	gorm.Model
	StackID uint   `gorm:"index:idx_name_optional_parameter,unique"`
	Name    string `gorm:"index:idx_name_optional_parameter,unique"`
}

I'm trying to get an instance with parameters and the StackRequiredParameter/StackOptionalParameter retrived as well since I need the Name property.

I can do it with the following function

func (r instanceRepository) FindWithParametersById(id uint) (*model.Instance, error) {
	var instance *model.Instance
	err := r.db.First(&instance, id).Error
	if err != nil {
		return nil, err
	}

	var requiredParameters []model.InstanceRequiredParameter
	err = r.db.
		Where("instance_id = ?", id).
		Preload("StackRequiredParameter").
		Find(&requiredParameters).Error
	if err != nil {
		return nil, err
	}

	var optionalParameters []model.InstanceOptionalParameter
	err = r.db.
		Where("instance_id = ?", id).
		Preload("StackOptionalParameter").
		Find(&optionalParameters).Error
	if err != nil {
		return nil, err
	}

	instance.RequiredParameters = requiredParameters
	instance.OptionalParameters = optionalParameters

	return instance, nil
}

But that takes 3 queries. Can anyone suggest a way to do it with just one query using Gorm?

You should be able to do that with something like this:

func (r instanceRepository) FindWithParametersById(id uint) (*model.Instance, error) {
    var instance *model.Instance
    err := r.db.Preload("RequiredParameters.StackRequiredParameter").Preload("OptionalParameters.StackOptionalParameter").First(&instance, id).Error
    if err != nil {
        return nil, err
    }

    return instance, nil
}

It will still perform multiple queries in your database, because each Preload call runs a separate query, but at least you don't have to write all that code.