Not（）属于错误的表

Question

I am using go version go1.10.3 linux/amd64 and mysql 5.7.

Need to runnable with GORM's docker compose config or please provides your config.

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mssql"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    _ "github.com/jinzhu/gorm/dialects/postgres"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

var db *gorm.DB

func init() {
    var err error

     db, err = gorm.Open("mysql", "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True")

    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

type Res {
    Id int  `gorm:"column:id"`
    age int  `gorm:"column:age"`
}

func main() {
        var result []Res
    db.Table("A").Select("A.id,A.age").Joins("left join B on A.id=B.id").
        Where("A.age=28").
       Not("B.id", []{2,3,4,5}).
       Scan(&result)
       fmt.Printf("%v", result)
}

The sql log is:

select A.id,A.age from A left join B on A.id=B.id where a.age=28 and A.B.id not in(2,3,4,5)

As can be seen, the not operation is appended for the table A (A.B.id not in ...). How can it be appended to table B (B.id not in ...)?

Answer 1

First off, as per my comment: You're only using mysql as a DB, and yet you're importing all the dialect packages (which do call their respective init functions. These functions register callbacks that are dialect-specific (e.g. the init func in the MsSQL package). Remove all the dialects you're not using from your imports:

// remove lines that I've commented out here...
import (
    "github.com/jinzhu/gorm"
    // _ "github.com/jinzhu/gorm/dialects/mssql"
     _ "github.com/jinzhu/gorm/dialects/mysql"
    // _ "github.com/jinzhu/gorm/dialects/postgres"
    // _ "github.com/jinzhu/gorm/dialects/sqlite"
)

You could move the NOT IN part of the WHERE clause to the JOIN condition based off of the documentation.

I'd also check any errors that you may encounter, they might give you more debug information on top of the log:

err := db.Table("A").Select("A.id,A.age").
    Joins("LEFT JOIN B on A.id =  B.id AND B.id NOT IN (?)", []int{2, 3, 4, 5}).
    Where("age = ?", 28).
    Scan(&result).Error
if err != nil {
    fmt.Fatalf("Failed to execute query: %+v", err)
}
fmt.Prinln(result)