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?
--
You received this message because you are subscribed to the Google Groups
"golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to golang-nuts+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
