In Gorm how can I use preload and distinct together?

I have following nested structs in my Go Code:

type Notification struct {
    SerialNumber      string   `json:"SerialNumber,omitempty"`
   	Ts                time.Time `json:"Ts,omitempty"`
	Notificationkeys  []*NotificationKeys   `json:"Notificationkeys,omitempty" gorm:"foreignKey:NotificationID"` 
    ID                uint64                 `gorm:"primaryKey;autoIncrement:true" json:"-"`
}

type NotificationKeys struct {
     Key            string  `json:"Key,omitempty"`
     Value          string  `json:"Value,omitempty"`
     ID             uint64  `gorm:"primaryKey;autoIncrement:true" json:"-"`
}

I use GORM for receiving notifications with following function:

type repo struct {
     db *gorm.DB
}


func (r repo) GetNotifications(serialNumber string, ts time.Time) ([]*Notification, error) {
     retval := []*Notification{}
     if tx := tr.db.Model(&Notification{}).Preload(clause.Associations).Where("serial_number=? AND ts <= ?", serialNumber, ts).Order("ts desc").Limit(3).Find(&retval); tx.Error != nil {
	      return retval, tx.Error
     }
     return retval, nil
}

Hitherto, this code is working: GORM loads my most recent three notifications with its key-value pairs into my retval array.

However, due to a bug I have notifications in my database that are dublicates and thus have idential timestamps.

Both SQL Server and GORM offers the DISTINCT function for this. I tried changing my function at various places for example like this:

func (r repo) GetNotifications(serialNumber string, ts time.Time) ([]*Notification, error) {
     retval := []*Notification{}
     if tx := tr.db.Model(&Notification{}).Preload(clause.Associations).Where("serial_number=? AND ts <= ?", serialNumber, ts).Order("ts desc").Distinct("ts").Limit(3).Find(&retval); tx.Error != nil {
	      return retval, tx.Error
     }
     return retval, nil
}

GORM does not deliver any notifications with identical timestamp now, however my NotificationKeys are not loaded anymore, the array is empty.Are those two functions not combineable or am I missing anything else?

I was able to achieve your goal with the following code. First, let me share the code, and then I'll walk you through all of the relevant sections.

package main

import (
	"fmt"
	"time"

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

type Notification struct {
	ID               uint64              `gorm:"primaryKey;autoIncrement:true" json:"-"`
	SerialNumber     string              `json:"CraneSerialNumber,omitempty"`
	Ts               time.Time           `json:"Ts,omitempty"`
	NotificationKeys []*NotificationKeys `json:"Notificationkeys,omitempty"`
}

type NotificationKeys struct {
	ID             uint64 `gorm:"primaryKey;autoIncrement:true" json:"-"`
	Key            string `json:"Key,omitempty"`
	Value          string `json:"Value,omitempty"`
	NotificationId uint64
}

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

	// seed some dummy data
	db.Create(&Notification{ID: 1, SerialNumber: "001", Ts: time.Date(2023, 7, 1, 10, 30, 0, 0, time.UTC)})
	db.Create(&Notification{ID: 2, SerialNumber: "001", Ts: time.Date(2023, 7, 1, 10, 30, 0, 0, time.UTC)})
	db.Create(&Notification{ID: 3, SerialNumber: "003", Ts: time.Date(2023, 7, 1, 10, 35, 0, 0, time.UTC)})
	db.Create(&NotificationKeys{Key: "a", Value: "1", NotificationId: 1})
	db.Create(&NotificationKeys{Key: "b", Value: "1", NotificationId: 2})
	db.Create(&NotificationKeys{Key: "c", Value: "1", NotificationId: 3})

	// get logic
	var notifications []Notification
	err = db.Debug().
		Model(&Notification{}).
		Preload(clause.Associations).
		Where("id in (?)", db.Debug().Model(&Notification{}).Group("serial_number,ts").Select("min(id) as id").Limit(2)). // subquery
		Order("ts desc").
		Find(&notifications).Error
	if err != nil {
		panic(err)
	}

	for _, v := range notifications {
		fmt.Println(v)
		for _, vv := range v.NotificationKeys {
			fmt.Println(*vv)
		}
	}
}

The code is mainly divided into three sections.

The structs definition

I had to adjust the definition of the NotificationKeys struct by adding the following field:

NotificationId uint64

The remaining code should be okay.

Bootstrap logic

You can merely ignore it. I run a Postgres container via Docker. Then, I created a Gorm DB client to interact with. Plus, I added some dummy data to the DB to be able to repro the issue.

The get logic

I was able to solve the issue by using the following methods (only the most relevant have been mentioned):

• Preload: to load the relationship notifications
• Subquery: written inline but you can extrapolate it from the Where. This groups the record of the notifications table by serial_number and ts thus filtering out the duplicated records

Thanks to this query that you can split into two smaller ones, you should be able to achieve your goal.
The final code has been added just to loop through the results we got.
Let me know if everything is okay or if you need something else, thanks!