Search code examples
gogo-gorm

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?


Solution

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