Left joins with inline and optional relationships?

[cmoad]

I've referred to https://tour.upper.io/sql-builder/02 but the example doesn't show how to deal with optional relationships / left joins.

In this made up example, a parent has zero or more children and a child has exactly one parent. (Don't question it. Just go with it.) I want a query that grabs all children for a specific parent.

type Parent struct {
  ID int64 `db:"id"`
}

type Child struct {
  ID int64 `db:"id"`
  ParentID int64 `db:"parent_id"
}

type ParentChild struct {
  ParentID `db:"pid"`
  Parent `db:",inline"`
  Child `db:",inline"`
}

q := sess.Select("parent.id AS pid", "*").
  From("parent").LeftJoin("children").On("parent.id = child.parent_id").
  Where("parent.id = 1")

I'll always get back one or more rows (assuming there is a parent with id = 1), but I can't figure out how to scan into the inline Child without getting an error like:

panic: sql: Scan error on column index N: converting driver.Value type <nil> ("<nil>") to a int64: invalid syntax

Is there a clean way to handle this without writing a completely new struct for Child that supports null types?

[xiam]

Hello @cmoad,

Can you try setting ParentID to *int64?:

type Child struct {
  ID int64 `db:"id"`
  ParentID *int64 `db:"parent_id"
}

type ParentChild struct {
  ParentID `db:"pid"`
  Parent `db:",omitempty,inline"`
  Child `db:",omitempty,inline"`
}

That way you'll be able to have nil ParentIDs.

Also, this looks similar to the case you're trying to solve:

https://github.com/upper/db/pull/393

The proposal above would do the same with less code, the omitempty tag will work for optional embedded params and the relation will be preloaded automatically, what do you think of it?

[cmoad]

I could but that would require dereferencing the pointer every time the variable is accessed. That isn't ideal and I was hoping for a more general purpose solution.

Sqlx supports embedding by prefixing each child relationship with a tag. E.g. select p.id, c.id as "child.id", c.parent_id as "child.parent_id" from .... While this makes the query syntax more verbose, you can easily embed one model struct inside another and scan without declaring everything as pointers.

The assoc tag looks interesting. I don't fully understand it, but in general I am finding that the most painful thing with sql/go is performing joins across many tables and scanning the results. Particularly with using left joins which yield null results. Sometimes hundreds of lines of code are required to fully scan the results and populate models correctly. Upper looks like it could really help too, but I'm struggling to find the best way.

[ilyar]

@cmoad try

type Child struct {
	ID       int64         `db:"id"`
	ParentID sql.NullInt64 `db:"parent_id"`
}