Group by column and scan into custom struct

Question

I have the following model. Each instance is part of a group which is only defined as the string GroupName here because the actual group is defined in a different service using a different database.

type Instance struct {
    gorm.Model
    UserID    uint
    Name      string `gorm:"index:idx_name_and_group,unique"`
    GroupName string `gorm:"index:idx_name_and_group,unique"`
    StackName string
    DeployLog string `gorm:"type:text"`
    Preset    bool
    PresetID  uint
}

I'd like to scan, the above model, into the following struct. Thus grouping instances why their group name.

type GroupWithInstances struct {
    Name      string
    Instances []*model.Instance
}

I'm been trying my luck with the following gorm code

var result []GroupWithInstances
err := r.db.
    Model(&model.Instance{}).
    Where("group_name IN ?", names).
    Where("preset = ?", presets).
    Group("group_name").
    Scan(&result).Error

indent, _ := json.MarshalIndent(result, "", "  ")
log.Println(string(indent))

But I'm getting the following error

ERROR: column "instances.id" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803)

I'm not sure how to deal with that since I don't want to group by instances but rather their groups.

score 0 · Answer 1 · answered Feb 15 '23 at 09:51

The error indicates that your RDBMS is in Full Group By Mode - cannot select field that isn't in group by clause or used in an aggregate function (SUM, AVG,...). There are 2 solutions:

Disable Full Group By mode. Example in MySQL
Modify the query

Even when we go with Solution 1, gorm will throw another error about relationship between GroupWithInstances and Instance.

So I think we should review the feature and go with Solution 1 - only select what is needed.

Group by column and scan into custom struct

1 Answers1