GORM插入子查询结果

huangapple go评论76阅读模式
英文:

GORM Inserting a subquery result

问题

除了使用原始的 SQL,是否有一种方法可以在 GORM 中使用子查询来实现插入操作?

我有以下定义:

type Customer struct {
    ID        string  
    Name      string
    OwnerID   string
    ...
}

type PaymentMethod struct {
    ID         string
    CustomerID string // 引用 Customer.ID
    Vendor     string
    Month      int
    Year       int
    ...
}

我想通过 OwnerID 查找客户,并为该用户插入一种支付方式。
如果我使用原始的 SQL,我会写出类似以下的语句:

INSERT INTO payment_method (ID, CustomerID, Month, Year)
SELECT (ID, 12, 2022)
FROM customer
WHERE owner_id = <some_value>

是否有一种方法可以在 GORM 中以单个查询实现这个功能?

英文:

Is there a way (besides using raw SQL) to implement an insert in gorm with a subquery?

I have the following definitions

type Customer struct {
	ID        string  
    Name      string
    OwnerID   string
    ...
}

type PaymentMethod struct {
    ID string
    CustomerID // references Customer.ID
    Vendor string
    Month int
    Year int
    ...
}

I want to find a customer by OwnerID and then to insert a payment method for that user.
If I were to use raw SQL, I would write something along the lines of:

INSERT INTO payment_method (ID, CustomerID, Month, Year)
SELECT (ID, 12, 2022)
FROM customer
WHERE owner_id = &lt;some_value&gt; 

Is there a way to implement it in GORM in a single query?

答案1

得分: 1

请查看下面的代码片段。
我使用行表达式通过所有者ID获取客户ID。

selectID := clause.Expr{
 SQL: "(SELECT id FROM customer WHERE owner_id = ?)",
 Vars: []interface{}{
  1, // 所有者ID
 },
}

values := map[string]interface{}{
 "customer_id": selectID,
 "month":       12,
 "year":        2022,
 "created_at":  time.Now(),
 "updated_at":  time.Now(),
}

err = db.Table("payment_method").Create(values).Error
if err != nil {
	fmt.Printf("%s", err)
}
  • 使用了以下模型。
type Customer struct {
	gorm.Model
	Name    string
	OwnerID string
}

type PaymentMethod struct {
	gorm.Model
	Vendor     string
	CustomerID int
	Month      int
	Year       int
}
英文:

Check the below code snippet.
I used row expressions to get the customer id by owner id.

selectID := clause.Expr{
 SQL: &quot;(SELECT id FROM customer WHERE owner_id = ?)&quot;,
 Vars: []interface{}{
  1, // Owner id
 },
}

values := map[string]interface{}{
 &quot;customer_id&quot;: selectID,
 &quot;month&quot;:       12,
 &quot;year&quot;:        2022,
 &quot;created_at&quot;:  time.Now(),
 &quot;updated_at&quot;:  time.Now(),
}

err = db.Table(&quot;payment_method&quot;).Create(values).Error
if err != nil {
	fmt.Printf(&quot;%s&quot;, err)
}
  • Following models were used.
type Customer struct {
	gorm.Model
	Name    string
	OwnerID string
}

type PaymentMethod struct {
	gorm.Model
	Vendor     string
	CustomerID int
	Month      int
	Year       int
}

huangapple
  • 本文由 发表于 2023年2月4日 14:41:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/75343438.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定