pg Inserting with relationships

Inserting with relationships

Open threeaccents opened this issue 7 years ago • 6 comments

Several times when inserting a model that has relationships we want to insert its relationships along with it. This is achievable in one query using just raw sql but then we lose all the advantages that go-pg offers. For example a companies and services table were companies has-many services.

type Company struct {
    ID string
    Name string
    Services []*Service
}

type Service struct {
    ID string
    CompanyID string
    Name string
}

And the sql tables would look smtn like this:

companies:
  - id string, primary key
  - name string, not null

services:
  - id string, primary key
  - company_id string, not null, foreign key (company.id)
  - name string, not null

We can insert this in one query pretty easily with raw sql:

WITH company AS ( INSERT INTO companies(id, name) VALUES('1', 'acme') RETURNING id)
INSERT INTO services(id, company_id, name) VALUES
('1', (select company.id from company), 'cool service'),
('2', (select company.id from company), 'cooler service');

Now to do something similar using go-pg we would have to use the db.Prepare statement. Note the code may be ugly but serves the purpose to show go-pg support would be helpful.

c := &Company{
    ID: uuid.NewV4().String(),
    Name: "test comp",
}

s := []*Service{
    &Service{
        ID: uuid.NewV4().String(),
        Name: "test svc",
    },
}

c.Service = s

values := []interface{}{
    c.ID,
    c.Name,
}

q := `
    WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2) RETURNING id) INSERT INTO services(id, company_id, name) VALUES
`

var i int = 3
for _, row := range c.Services {
    q += fmt.Sprintf("($%d, (select company.id from company), $%d),", i, i+1)
    values = append(values, row.ID, row.Name)
    i += 2
}

q = strings.TrimSuffix(q, ",")

stmt, err := DB.Prepare(q)
if err != nil {
    return err
}

if _, err := stmt.Exec(values...); err != nil {
    return err
}

Now the problem with this besides how much work and different ways to go about this is we lose the null wrapper go-pg provides. Now for all my string instances or int instances I need to wrap them with a function to transform them to sql null values or use pointers for all my types (feels iffy). Which makes our code event uglier

func newNullString(s string) sql.NullString {
	if len(s) == 0 {
		return sql.NullString{}
	}
	return sql.NullString{
		String: s,
		Valid:  true,
	}
}

// updating the values interface to use the function

...
values := []interface{}{
    newNullString(c.ID),
    newNullString(c.Name),
}
...

Instead it would be nice if when running db.Insert it could automatically check for struct arrays that usually means a table relationship and if its not null in the struct to insert all the relationships. Or maybe adding a new db.InsertWithRelations() method if adjusting db.Insert() would be to much work.

As a side note I tried finding your email to send you a private message but couldn't find it so I'm gonna do it here. I've been using your package now for a few months and I love it I want to start contributing to it and was wondering if there was anything specifically you would like an extra hand with. Let me know and great job on a great package

Feb 25 '17 23:02 threeaccents

Hi,

This looks like a good idea, but it requires some work and time to get it done. I would appreciate any help, but unfortunately code that works with relations is rather complex and doesn't have any comments so you will have a tough time reading it. But here are some advices:

start with InsertCascade so backwards compatibility is not broken
orm.newTableModel creates orm.tableModel from a struct or a slice which is a starting point
tableModel.Table() returns Table which has Relations map[string]*Relation with a map of all relations of the model
Relation.Field.Index gives index of the field for relation that can be used with reflect package

Then by iterating over relations you can start building your query using existing orm.insertQuery and With/WrapWith method. This is very rough overview and it is hard to say if existing APIs cover your needs or not, but it is all I can do without really trying to implement this.