Joining children records as a slice

[KennFatt]

hello, i want to join my tables (one-to-many relation) and map them as a slice in the parent field.

here is my structs:

type User struct {
	ID          uint64     `db:"id" fieldopt:"omitempty"`
	CreatedAt   time.Time  `db:"created_at" fieldopt:"omitempty"`
	UpdatedAt   time.Time  `db:"updated_at" fieldopt:"omitempty"`
	DeletedAt   *time.Time `db:"deleted_at" fieldopt:"omitempty"`
	Versions    uint64     `db:"versions" fieldopt:"omitempty"`
	FirstName   string     `db:"first_name"`
	LastName    *string    `db:"last_name" fieldopt:"omitempty"`
	Email       string     `db:"email"`
	PhoneNumber *string    `db:"phone_number" fieldopt:"omitempty"`
	Password    string     `db:"password"`

	Expenses []*Expense `db:"expenses" fieldopt:"omitempty" fieldtag:"fk"`
}

var UserStruct = sqlbuilder.NewStruct(new(User))

type Expense struct {
	ID        uint64     `db:"id" fieldopt:"omitempty"`
	CreatedAt time.Time  `db:"created_at" fieldopt:"omitempty"`
	UpdatedAt time.Time  `db:"updated_at" fieldopt:"omitempty"`
	DeletedAt *time.Time `db:"deleted_at" fieldopt:"omitempty"`
	Versions  uint64     `db:"versions" fieldopt:"omitempty"`

	CreatedBy uint64 `db:"created_by"`
	UpdatedBy uint64 `db:"updated_by"`

	UserID    uint64    `db:"user_id"`
	Name      string    `db:"name"`
	Detail    *string   `db:"detail" fieldopt:"omitempty"`
	Amount    float64   `db:"amount"` // TODO: change it to decimal
	EventDate time.Time `db:"event_date"`
}

var ExpenseStruct = sqlbuilder.NewStruct(new(Expense))

Is it possible to query the users table and join the expenses together then map them as []*Expense slice in the User struct?

[huandu]

Struct doesn't work well with JOIN expression. It was designed to map rows in one table to a Go struct. I need to think of how to support this case.

[KennFatt]

yes, the Struct really helps when i want to simply use my SELECT and map them to a Go struct.

I'm coming from an ORM like Ent and Jet SQL Builder, so i'm looking to do something that can also map my subqueries or children as a slice. I know that Struct is using the word "lightweight ORM", if it's possible to enhance it a little bit more, this lib would be a sweet spot between ORM and SQL Builder.

IIRC Jet could do that (partially select the children columns and JOIN-ing them) and then map them as a slice in the parent's struct.

[hiSandog]

This sounds like a one-to-many result-shaping problem rather than only SQL string building. Do you expect go-sqlbuilder to generate SQL aggregation such as JSON arrays, or to provide a scanning/helper layer that groups joined rows into parent structs with child slices? A small input schema and desired Go output shape would help pick the right abstraction.

[huandu]

@hiSandog

This sounds like a one-to-many result-shaping problem rather than only SQL string building.

Exactly. The PR #233 partially fixes this issue by introducing a mechanism to map query result to a complex nested struct. However, one-to-many result-shaping problem is still open and could be out-of-scope of this package.

This package is designed to be a string builder for SQL. The ORM part is very thin and only depends on database/sql package which provides very limited deserialization support. I don't think this package should become a full featured ORM.