database/sql left join best practicea for null values

[slinso]

What's a nice way to handle left joins with database/sql? Imagine you have a users who can optional have an image. So you have an optional 1:1 relationship.

Example:

type User struct {

ID         int

Loginname  string

Password   string
        ImageID    int // or sql.NullInt depends on your DB design
//...

Image    *Images

}

type Images struct {

ID          int

Name        string
// ...

}

If you use some orm like gorm querying for a user with an relationship will result in 2 queries. wich is a simple way of hanlding this, but one statement with a left join would be cleaner imho.

SELECT U.*, I,* FROM User U LEFT JOIN Image I  ON (U.ImageID = I.ID) WHERE U.ID = 1

The problem is, that normally your Image attributes can't be null, but in this situation all attributes may be null. So a normal QueryRow().Scan() with the abocve types is problematic.

What do I want to achive?

1. I could define an ImageScan type that only has pointers oder sql.Null... And a method like convertToImage() which returns am Image. But than I would have an extra layer just for scanning join statements.

2. Scan all columns to []interface{} an only construct the Image type if it is found. this could be used with a code generations approach otherwise it sounds like a lot of writing. But you will loos type information and the interface{} values will all be of type []uint8 or int64. Would help if you could Scan a single column Value with database/sql.

Example:

data := User{}

cols := u.db.QueryRowx(sqlstmt, args...).SliceScan()

data.ID = int(cols[0].(int64))

data.Loginname = string(cols[1].([]uint8))

data.Password = string(cols[2].([]uint8))

if cols[3] != nil {

data.Image = &Image{}

data.Image.ID = int(cols[4].(int64))

// ...

}

3. Make two queries like most orms are doing.

Is there maybe a nice way to solve this problem? Have I overlooked something in the databas/sql docs?

[slinso]

Forgot to mention that I currently use MySQL with go-sql-driver.

Another solution would be to use mymysql as a driver. That one maps null values to go type default zero values.

But I wanted to stick to go-sql-driver because I think it is more actively maintained.

[mey...@traum-ferienwohnungen.de]

Just found myself in the same situation wondering that this should not be so complicated.
What did you end up doing may I ask?