Gorm预载荷m2m关系

polhcujo  于 2023-05-11  发布在  Go
关注(0)|答案(2)|浏览(141)

我想用gorm预加载M2M关系,但它没有用Preload函数填充切片。

这是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)
);

这些是我的高姆模型

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
}

我想退还捐款和它的细节,包括项目信息.就像这样:

{
  "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"
      }
    }
  ]
}

我正在尝试使用以下代码预加载DonationDetails切片,但结果是空切片:

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
}

sql debug的输出显然打印了这3个查询:

[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

注意:我是在.sql文件中手动迁移,没有使用Gorm Automigrate功能。也许我错过了一些结构标记注解,或者我完全误解了这是如何工作的。
PS:我只是想做这样的事情

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

用gorm做这件事的理想方法是什么?必须有人知道我只是想做一个愚蠢的加入,它能有多难。

mlnl4t2r

mlnl4t2r1#

有几件事需要尝试和修复:
您可能不需要many2many属性来加载DonationDetail切片,因为它们只能用DonationID加载。如果你有一个外键,你可以这样添加它:

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"`
}

First方法中不需要&d,因为d已经是一个指针。但是,要加载Project数据,还需要预加载。

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
}
deikduxw

deikduxw2#

您可以使用关联来实现这一点。
如果您已经找到了您的捐赠对象,这是可以的。

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

相关问题