How to use JOIN in Building Queries?

[kolkov]

Whether it is possible to use query builder?

[qiangxue]

yes, db.Select().Join(...) See SelectQuery

[kolkov]

Thanks! But how can I use "ON"?

[qiangxue]

See https://godoc.org/github.com/go-ozzo/ozzo-dbx#SelectQuery.InnerJoin

[kolkov]

ok! thanks again) Is it possible log SQL errors to dbx logger?

[kolkov]

When I using LEFT OUTER JOIN I can not populate all rows into variable. q.All populate all rows, but stop working on first row with NULL value from joined table. Is it correct?

[qiangxue]

Did you check the error returned? Are you using the latest code?

[kolkov]

Exec err: sql: Scan error on column index 6: unsupported Scan, storing driver.Value type <nil> into type *string I use the code a week ago

[qiangxue]

Please do an update and try again.

[qiangxue]

There's a unit test covering this case: https://github.com/go-ozzo/ozzo-dbx/blob/master/query_test.go#L152

[kolkov]

nothing has changed in result Exec err: sql: Scan error on column index 6: unsupported Scan, storing driver.Value type into type *string

[qiangxue]

Does the unit test I showed resemble to what you have problem with? Do you have code and data that can reproduce the issue? What DBMS are you using?

[kolkov]

D:\Go\src\github.com\go-ozzo\ozzo-dbx>go test ./...
--- FAIL: TestDB_MustOpen (0.00s)
        Error Trace:    db_test.go:36
        Error:          Expected nil, but got: &mysql.MySQLError{Number:0x415, Message:"Access denied for user 'travis'@'
localhost' (using password: NO)"}

--- FAIL: TestDB_Begin (0.00s)
panic: Error 1045: Access denied for user 'travis'@'localhost' (using password: NO) [recovered]
        panic: Error 1045: Access denied for user 'travis'@'localhost' (using password: NO)

goroutine 144 [running]:
panic(0x6d22c0, 0xc0421634e0)
        C:/Go/src/runtime/panic.go:500 +0x1af
testing.tRunner.func1(0xc042176fc0)
        C:/Go/src/testing/testing.go:579 +0x264
panic(0x6d22c0, 0xc0421634e0)
        C:/Go/src/runtime/panic.go:458 +0x251
github.com/go-ozzo/ozzo-dbx.getPreparedDB(0xc0420904e0)
        D:/Go/src/github.com/go-ozzo/ozzo-dbx/db_test.go:271 +0x1b2
github.com/go-ozzo/ozzo-dbx.TestDB_Begin(0xc042176fc0)
        D:/Go/src/github.com/go-ozzo/ozzo-dbx/db_test.go:152 +0x51
testing.tRunner(0xc042176fc0, 0x758700)
        C:/Go/src/testing/testing.go:610 +0x88
created by testing.(*T).Run
        C:/Go/src/testing/testing.go:646 +0x2f3
FAIL    github.com/go-ozzo/ozzo-dbx     0.176s

D:\Go\src\github.com\go-ozzo\ozzo-dbx>

[qiangxue]

To run the unit test, you need to set up the database according to the instructions in testdata/mysql.sql

What I was asking is that if the unit test is similar to what you are doing...

[kolkov]

D:\Go\src\github.com\go-ozzo\ozzo-dbx>go test ./...
ok      github.com/go-ozzo/ozzo-dbx     3.392s

D:\Go\src\github.com\go-ozzo\ozzo-dbx>

[kolkov]

My code is:

func (c *Casting) GetAll() *Castings {
    q := db.Select("casting.id", "firstname", "lastname", "patronymic", "birthday", "casting-date", "character.name AS character-name").
        From("casting").OrderBy("id").
        Join("LEFT OUTER JOIN","character", dbx.NewExp("`character`.`id` = `casting`.`character_id`"))

    var castings *Castings
    err := q.All(&castings)
    if err != nil {
        println("Exec err:", err.Error())
    }
    return castings

[kolkov]

Sql tables:

sql.zip

[qiangxue]

How do you define Casting and Castings?

[kolkov]

type Casting struct {
    Id int `json:"id" db:"id"`
    FirstName string `json:"FirstName" db:"firstname"`
    LastName string `json:"LastName" db:"lastname"`
    Patronymic string `json:"Patronymic" db:"patronymic"`
    Birthday string `json:"Birthday" db:"birthday"`
    CastingDate string `json:"CastingDate" db:"casting-date"`
    Character string `json:"CharacterName" db:"character-name"`
    CharacterId int `json:"characterId" db:"character_id"`
}

type Castings []Casting

[qiangxue]

There's no NULL data or column in your sql scripts...

[kolkov]

Yes, but if I use LEFT OUTER JOIN some data have NULL cells:

[qiangxue]

Ah, I see. You should change the type of Character field to be either *string or sql.NullString. This is because string cannot be nil. This is a common requirement when handling database nulls golang.

[kolkov]

Yes, I tried this. It's Working, but isn't good solution. Is it possible to change null to empty string in ozzo-dbx on the fly (optionaly)? I think that will be a good solution.

[qiangxue]

I agree with you this is ugly. But unfortunately it's not very feasible to implement what you suggested because before receiving the query result, ozzo cannot know if the result may contain null or not. So it would have to create pointers in all scenarios, which makes query very inefficient.

Also, using sql.NullXyz is the recommended way. So perhaps you should stick to it.

[kolkov]

Ok! Thank you very much! )