Gorm preload m2m relation

i want to preload M2M relation with gorm and it is not populating the slice with Preload function.

This is the sql schema

create table project (
  id int generated by default as identity,
  name varchar(64) not null,
  slug varchar(64) not null,
  image_url text,
  constraint project_pk primary key (id),
  constraint project_unq unique (name, slug)
);

create table donation (
  id int generated by default as identity,
  user_id varchar(36) not null,
  total_amount numeric(7, 2) not null,
  currency varchar(3) not null,
  constraint donation_pk primary key (id)
);

create table donation_detail (
  donation_id int not null,
  project_id int not null,
  amount numeric(7, 2) not null,
  primary key (donation_id, project_id)
);

These are my gorm models

type Donation struct {
    ID              uint64            `json:"id" gorm:"primarykey"`
	UserID          string            `json:"user_id"`
	PaypalOrderID   string            `json:"paypal_order_id"`
	TotalAmount     float64           `json:"total_amount"`
	Currency        string            `json:"currency"`
	DonationDetails []*DonationDetail `json:"donation_details" gorm:"many2many:donation_detail;"`
}

type Project struct {
    ID          uint64  `json:"id" gorm:"primarykey"`
	Name        string  `json:"name"`
	Slug        string  `json:"slug"`
	ImageURL    string  `json:"image_url"`
}

type DonationDetail struct {
	DonationID uint64  `json:"donation_id" gorm:"primaryKey"`
	ProjectID  uint64  `json:"project_id" gorm:"primaryKey"`
	Amount     float64 `json:"amount"`
	Project    Project
}

I want to return the donation and and it's details that include project information. something like this:

{
  "id": 1,
  "user_id": "e14a98d1-0c4a-45c3-b748-5ac6ba733b99",
  "paypal_order_id": "6SR91505YA360210R",
  "total_amount": 10000,
  "currency": "EUR",
  "donation_details": [
    {
      "donation_id": 1,
      "project_id": 1,
      "amount": 3000,
      "project": {
        "project_id": 1,
        "name": "First Project",
        "slug": "first_project",
        "image_url": "img1.jpg"
      }
    },
    {
      "donation_id": 2,
      "project_id": 2,
      "amount": 7000,
      "project": {
        "project_id": 2,
        "name": "Second Project",
        "slug": "second_project",
        "image_url": "img2.jpg"
      }
    }
  ]
}

i am trying this code to preload DonationDetails slice but it ends up being empty slice:

func List(donationID string) (*Donation, error) {
  var d *Donation

  if err := db.Debug().Preload("DonationDetails").First(&d, donationID).Error; err != nil {
    return nil, fmt.Errorf("could not list donation details: %w", err)
  }

  return d, nil
}

The output of sql debug apparently prints these 3 queries:

[rows:3] SELECT * FROM "donation_detail" WHERE "donation_detail"."donation_id" = 1
[rows:0] SELECT * FROM "donation_detail" WHERE ("donation_detail"."donation_id","donation_detail"."project_id") IN (NULL)
[rows:1] SELECT * FROM "donation" WHERE "donation"."id" = '1' ORDER BY "donation"."id" LIMIT 1

Note: I am doing migrations manually in .sql file and not using Gorm Automigrate functionality.
Maybe i am missing some struct tag annotation or am i completely misunderstanding how this works.

PS: i am just trying to do something like this

SELECT * FROM donation d 
 LEFT JOIN donation_detail dd on d.id = dd.donation_id 
 LEFT JOIN project p on p.id = dd.project_id

What is the ideal way to do this with gorm? Someone has to know. i am just trying to do a stupid join, how hard can it be.

There are a couple of things to try out and fix:

You probably don't need the many2many attribute to load the DonationDetail slice, since they can be loaded only with DonationID. If you have a foreign key, you can add it like this:

type Donation struct {
    ID              uint64            `json:"id" gorm:"primarykey"`
    UserID          string            `json:"user_id"`
    PaypalOrderID   string            `json:"paypal_order_id"`
    TotalAmount     float64           `json:"total_amount"`
    Currency        string            `json:"currency"`
    DonationDetails []*DonationDetail `json:"donation_details" gorm:"foreignKey:DonationID"`
} 

You don't need &d in the First method, since d is already a pointer. However, to load the Project data, you will need to preload as well.

func List(donationID string) (*Donation, error) {
  var d *Donation

  if err := db.Debug().Preload("DonationDetails.Project").First(d, donationID).Error; err != nil {
    return nil, fmt.Errorf("could not list donation details: %w", err)
  }

  return d, nil
}

You achieve this using Associations.

This is Ok when you already found your donation object which you already had.

var projects []Project
var donation Donation
conf.DB.First(&donation, "id = ?", donationID)
conf.DB.Model(&donation).Association("Donation").Find(&projects)