Just found myself in the same situation wondering that this should not be
so complicated.
What did you end up doing may I ask?
On Tuesday, 25 April 2017 09:29:24 UTC+2, slinso wrote:
>
> 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.
