jmoiron
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
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...
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.
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 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 Did you ever make that pr? If not, I might make a stab at it.
If you are using PostgreSQL you can use
COALESCEto 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.Hobbyis still of typestring, becauseNULLvalues will be coalesced into''. If theHobbyIdis not valid (hobby_idisNULL) then you can just ignore the coalesced values by settingperson.Hobby = nil.
This pattern works as well in MySQL & MariaDB
