Sqlx: StructScan rows after outer join

[Perlence]

Is there a way to simply embed e.g. "right" table into "left" table and make "LEFT OUTER JOIN"?
Columns on the right can be nil, but I don't feel that copy-pasting type definition replacing types with their Null counterparts is good idea.

type EmployeeDepartment struct {
  Employee
  Department
}

type Department struct {
  DepartmentID uint64
  DepartmentName string
}

type Employee struct {
  LastName string
  DepartmentID int64
}

func join(db *sqlx.DB) error {
  var eds []EmployeeDepartment
  err := db.Select(&eds, `SELECT *
    FROM employee 
    LEFT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;`)
  if err != nil {
    // err is not nil: "Scan error on column index 2: unsupported driver Scan pair: <nil> -> *string".
    return err
  }
  return nil
}

I tried to perform two separate scans and put them together:

func join(db *sqlx.DB) error {
  var eds []EmployeeDepartment
  rows, err := db.Queryx(`SELECT *
    FROM employee 
    LEFT OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;`)
  for rows.Next() {
    var e Employee
    err = rows.StructScan(&e)
    if err != nil {
      return err
    }
    var d Department
    // Here StructScan panics: "reflect: Field index out of range"
    err = rows.StructScan(&d)
    if err != nil {
      // Handle "unsupported driver Scan pair" and move along.
    }
    eds = append(eds, EmployeeDepartment{Employee: e, Department: d})
  }
  return nil
}

[nubbel]

+1

I would love to have sqlx allow this syntax:

type EmployeeDepartment struct {
  Employee
  *Department
}

[schickling]

+1

Current workaround involves a null-object in the database which feels really wrong

[schickling]

@jmoiron are there any plans to support this in the near future?

[jriquelme]

I'm facing the same situation. Would be nice to allow @nubbel proposal.

[kliron]

+1 Same here.

[veqryn]

I'm facing this issue as well.

Honestly I thought it was enough to just use sql.NullInt64 on the possibly-null-foreign-key (to borrow the example above):

type EmployeeDepartment struct {
  Employee
  Department
}

type Department struct {
  DepartmentID uint64
  DepartmentName string
}

type Employee struct {
  LastName string
  DepartmentID sql.NullInt64
}

However, I get the error:
sql: Scan error on column index 10: converting driver.Value type <nil> (\"<nil>\") to a int64: invalid syntax

I don't really feel like I should have to have a pointer to Department in the EmployeeDepartment struct. Just zero out the fields and let me check if employeeDepartment.Employee.DepartmentID.Valid() before I use employeeDepartment.Department.

Edit: I am fine with pointer idea or any idea, but I definitely need a solution to this...

[Alex1sz]

+1

[kletkavrubashku]

+1

[newhook]

Yeah, this is definitely a pain point for me. In general the columns in my tables are not nullable which means I have non-nulltable fields in my scannable structs. However, when I start doing joining I end up with all sorts of null fields meaning I need to either make my struct fields all nullable (and dealing with that mess in my code), or duplicating the structs for scanning the joined rows.

[blaskovicz]

I've been able to work around this by making _all_ of the fields in the nillable struct nillable themselves as well, but it isn't pretty since it will actually instantiate the B struct on an empty row, but all of its fields will be empty.

type B struct {
  ID *string `json:"id"`
  Name *string `json:"name"`
}
type A struct {
  ID string `json:"id"`
  B *B `json:"b"`
}

SELECT a.id AS "a.id", b.id AS "b.id", b.name AS "b.name"
FROM a LEFT OUTER JOIN b on b.a_id = a.id

[donutmonger]

If you are using PostgreSQL you can use COALESCE to get around making all your struct fields nullable.

Example:

CREATE TABLE hobbies (
    id INT PRIMARY KEY,
    hobby STRING NOT NULL,
    location STRING
);

CREATE TABLE people (
    id INT PRIMARY KEY,
    name STRING NOT NULL,
    hobby_id INT REFERENCES hobbies (id)
);

type Hobby struct {
    ID       int            `db:"id"`
    Hobby    string         `db:"hobby"`
    Location sql.NullString `db:"location"`
}

type Person struct {
    ID      int            `db:"id"`
    Name    string         `db:"name"`
    HobbyID sql.NullString `db:"hobby_id"`
    Hobby   *Hobby         `db:"hobby"`
}

func getPeople(sqlxDB *sqlx.DB) ([]Person, error) {
    people := []Person{}
    query := `SELECT
        people.id "id",
        people.name "name",
        people.hobby_id "hobby_id",
        COALESCE(hobbies.hobby, '') "hobby.hobby",
        COALESCE(hobbies.location, '') "hobby.location"
    FROM
        people
    LEFT JOIN hobbies ON
        people.hobby_id = hobbies.id`

    if err := sqlxDB.Select(&people, query); err != nil {
        return nil, err
    }

    // if `hobby_id` is `NULL`, set `person.Hobby` is `nil`
    for i := range people {
        if !people[i].HobbyID.Valid {
            people[i].Hobby = nil
        }
    }
    return people, nil
}

Notice how Hobby.Hobby is still of type string, because NULL values will be coalesced into ''. If the HobbyId is not valid (hobby_id is NULL) then you can just ignore the coalesced values by setting person.Hobby = nil.

[tomwassing]

+1

[austinh]

Is this something that can be supported? LEFT OUTER JOIN is pretty common. Would it be bad for sqlx to just check if the resulting join is null, and if its, do not instantiate the struct? This seems pretty standard for a struct serialization library with join support.

[ywilkof]

This should be supported, in my opinion. It practically blocks scanning results of LEFT JOIN queries that might yield no matching value.

[yousseftelda]

@jmoiron This would be pretty nice to add to SQLx. I can put together a PR if we agreed on the behavior, what do you think?