CODEWITHSUNDEEP
gogo-gorm
Pavel Ryvintsev
Pavel Ryvintsev

Reputation: 1008

GORM Inserting a subquery result

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 = <some_value>

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

Upvotes: 2

Views: 485

Answers (1)

Chamith Udayanga
Chamith Udayanga

Reputation: 574

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

selectID := clause.Expr{
 SQL: "(SELECT id FROM customer WHERE owner_id = ?)",
 Vars: []interface{}{
  1, // Owner 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)
}
  • 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
}

Upvotes: 1

Related Questions