LeftJoins on pointer structs doesn't fill data

[derkan]

Hi Samuel,

I hope you're well in these corona-days.

When using LeftJoin and scanning on it doesn't fill data if target struct is pointer. Please check this code:

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	
	"github.com/samonzeweb/godb"
	"github.com/samonzeweb/godb/adapters/sqlite"
)

type Author struct {
	ID        int    `db:"id" json:"id"`
	Firstname string `db:"firstname" json:"firstname"`
	Lastname  string `db:"lastname" json:"lastname"`
}

type Book struct {
	ID       int           `db:"id" json:"id"`
	AuthorID sql.NullInt64 `db:"author_id" json:"author_id"`
	Title    string        `db:"title" json:"title"`
}

type BookInfo struct {
	Book   `db:",rel=b" json:""`
	Author *struct {
		ID        sql.NullInt64  `db:"id" json:"id"`
		Firstname sql.NullString `db:"firstname" json:"firstname"`
		Lastname  sql.NullString `db:"lastname" json:"lastname"`
	} `db:",rel=a" json:"author,omitempty"`
}

func chkErr(msg string, err error) {
	if err != nil {
		log.Printf(msg, err)
		os.Exit(1)
	}
}

func main() {
	db, err := godb.Open(sqlite.Adapter, "./data.db")
	chkErr("Can not connect to db, err: %v", err)
	_, err = db.CurrentDB().Exec(`
--DROP TABLE books; DROP TABLE authors;
CREATE TABLE IF NOT EXISTS authors ( id INTEGER NOT NULL PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL );
CREATE TABLE IF NOT EXISTS books ( id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER, published DATE,
	FOREIGN KEY(author_id) REFERENCES authors(id));
INSERT INTO authors VALUES(1, 'Orhan','Pamuk') ON CONFLICT DO NOTHING ;
INSERT INTO books VALUES(1, 'My Name Is Red', 1, '1998-01-01') ON CONFLICT DO NOTHING;
INSERT INTO books VALUES(2,'Anonymous', NULL, NULL) ON CONFLICT DO NOTHING;`)
	chkErr("Can not migrate db, err: %v", err)
	
	var books []BookInfo
	err = db.SelectFrom("books as b").
		LeftJoin("authors", "a", godb.Q("b.author_id = a.id")).Do(&books)
	chkErr("Can not get books, err: %v", err)
	out, err := json.MarshalIndent(books, "", "   ")
	chkErr("Can not get marshall, err: %v", err)
	fmt.Printf("%s\n", out)
}

This prints output as:

[
   {
      "id": 1,
      "author_id": { "Int64": 1, "Valid": true},
      "title": "My Name Is Red"
   },
   {
      "id": 2,
      "author_id": { "Int64": 0, "Valid": false},
      "title": "Anonymous"
   }
]

Author is missing in output for book record no: 1. If I change struct to this it fills data, but this time fills for null author with null values as it is not pointer:

type BookInfo struct {
	Book   `db:",rel=b" json:""`
	Author struct {
		ID        sql.NullInt64  `db:"id" json:"id"`
		Firstname sql.NullString `db:"firstname" json:"firstname"`
		Lastname  sql.NullString `db:"lastname" json:"lastname"`
	} `db:",rel=a" json:"author,omitempty"`
}

Am I missing something?
Thanks

[samonzeweb]

Hi Erkan,

Sorry for the delay, but I was contaminated. I hope you're fine.

Godb is not designed to work this way, because it does not allocate nested structures, only the root one if needed (it depends on the target, slice of object, or slice of pointers to objects). This kind of job is closer to what will do a more featured tools like an ORM.

The way to cope with this is using only SQL.NullXXXX for fields on the optional part of the DB response.

I have to admit that it could be a problem with JSON serialisation. But using the same structure for both DB access and HTTP response is not a good practice for real products (expect simple ones).

Changing the way it works will not be a trivial thing.

Sam.

[derkan]

Thank you for response and I wish you get well soon.
About me, I'm fine thanks, we're locked in.
You are right about using same structure for DB and HTTP response but otherwise code gets lots of overhead. I'll check a way around. Closing this issue for now.