StructScan rows after outer join

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
}

+1

I would love to have sqlx allow this syntax:

type EmployeeDepartment struct {
  Employee
  *Department
}

+1

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

@jmoiron

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

@nubbel

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

+1 Same here.

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...

+1

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.

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

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.

+1

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.

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

@jmoiron

@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?

@yousseftelda

@yousseftelda Did you ever make that pr? If not, I might make a stab at it.

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.

This pattern works as well in MySQL & MariaDB

Navigation Menu

Repository navigation

StructScan rows after outer join #162

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

StructScan rows after outer join #162

Description

Activity

nubbel commented on Oct 26, 2015

schickling commented on Nov 21, 2015

schickling commented on Nov 21, 2015

jriquelme commented on Mar 3, 2016

kliron commented on Mar 4, 2016

veqryn commented on Jan 12, 2017

Alex1sz commented on Apr 27, 2017

kletkavrubashku commented on Jun 29, 2017

newhook commented on Jul 3, 2017

blaskovicz commented on Apr 10, 2018

dev-rice commented on Jun 19, 2019

tomwassing commented on Jul 16, 2019

austinh commented on Dec 9, 2019

ywilkof commented on Apr 6, 2020

yousseftelda commented on Jun 28, 2020

byrnedo commented on Nov 10, 2022

julio-alv commented on Dec 10, 2024

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions