GORM return list of list of results or map of results with group by id

Question

Essentially, using GORMDB, my current code looks something like this:

res = []*modelExample

DB.Model(&modelExample{}).
        Order("task_id ").
        Find(res)

And what I do with res is that I will manually loop through and append the models with the same task_id into one list, and then append this list to be worked on. The reason why I need to do this is because there are some specific operations i need to do on specific columns that I need to extract which I can't do in GORM.

However, is there a way to do this more efficiently where I return like a list of list, which I can then for loop and do my operation on each list element?

Answer 1

You should be able to achieve your needs with the following code snippet:

package main

import (
    "fmt"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type modelExample struct {
    TaskId int
    Name   string
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&modelExample{})

    // here you should populate the database with some data

    // querying
    res := make(map[int][]modelExample, 0)
    rows, err := db.Table("model_examples").Select("task_id, name").Rows()
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    // scanning
    for rows.Next() {
        var taskId int
        var name string
        rows.Scan(&taskId, &name)
        if _, isFound := res[taskId]; !isFound {
            res[taskId] = []modelExample{{taskId, name}}
            continue
        }
        res[taskId] = append(res[taskId], modelExample{taskId, name})
    }

    // always good idea to check for errors when scanning
    if err = rows.Err(); err != nil {
        panic(err)
    }

    for _, v := range res {
        fmt.Println(v)
    }
}

After the initial setup, let's take a closer look at the querying section.
First, you're going to get all the records from the table. The records you get are stored in the rows variable.
In the for loop, you scan all of the records. Each record will be either added as a new map entry or appended to an existing one (if the taskId is already present in the map).
This is the easiest way to create different lists based on a specific column (e.g. the TaskId). Actually, from what I understood, you need to split the records rather than grouping them with an aggregation function (e.g. COUNT, SUM, and so on).
The other code I added was just put in for clarity.
Let me know if this solves your issue or if you need something else, thanks!