Gorm Many2Many Relation Produces Unwanted where Condition

Your Question

Why many2many relations produces an unwanted where condition?

Reproduction Steps

I have a gorm model which use a many2many relation:

Package struct {
  ID        int32            `json:"id" gorm:"primaryKey"`
  Code      string           `json:"code" gorm:"unique"`
  Name      string           `json:"name"`
  Price     float32          `json:"price"`
  Branches  []*branch.Branch `json:"branches" gorm:"many2many:branch_products;joinForeignKey:product_id"`
  CreatedAt time.Time        `json:"created_at"`
  UpdatedAt time.Time        `json:"updated_at"`
  gorm.DeletedAt
}

type Branch struct {
  ID          int       `json:"id"`
  Code        string    `json:"code"`
  Name        string    `json:"name"`
  Description string    `json:"description" gorm:"default:null"`
  CreatedAt   time.Time `json:"created_at"`
  UpdatedAt   time.Time `json:"updated_at"`
  DeletedAt   gorm.DeletedAt
}

Where the packages belongs to many branches and uses a table called branch_products. I've use a model named Package because it will have different variety of products other than packages.

This is what branch_products table looks like:

branch_id int
product_id int

The database that i'm using is PostgreSQL.

When i try to create a package it works fine, but when i try to use Preload on the Package model using the Branches relation, the query fail and it produces an SQL like this:

SELECT * FROM "branch_products" WHERE "branch_products"."product_id" = 1 AND "branch_products"." IS NULL

Why would gorm add a:

AND "branch_products"." IS NULL

at the end of the query?

The document you expected this should be explained

This should be explained more in https://gorm.io/docs/many_to_many.html.

Expected answer

I just want to know if there's any specific database configuration for my model if i want to use a many2many relation. I've read the documentation about https://gorm.io/docs/many_to_many.html and it explains well about how to customize the foreign key name which i expect to work well with my case. Or maybe there could be a bug in the library itself.

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: gg1229505432

I need more detail code about this sql: SELECT * FROM "branch_products" WHERE "branch_products"."product_id" = 1 AND "branch_products"." IS NULL

Comment From: dwadp

I need more detail code about this sql: SELECT * FROM "branch_products" WHERE "branch_products"."product_id" = 1 AND "branch_products"." IS NULL

Sure.

Here's the table schema

And this is the code that's selecting specific product with the Preload("Branches")

func FindByID(id int32) (*Package, error) {
  var pkg Package

  if err := db.Preload("Branches").First(&pkg, id).Error; err != nil {
    return nil, err
  }

  return &pkg, nil
}

Comment From: dwadp

Is there any updates ? @gg1229505432 @jinzhu

Comment From: gg1229505432

type Branch struct {
    ID       int       `json:"id"`
    Name     string    `json:"name"`
    Packages []Package `json:"packages" gorm:"many2many:associations_table"`
}

type Package struct {
    ID       int32    `json:"id" gorm:"primaryKey"`
    Name     string   `json:"name"`
    Branches []Branch `json:"branches" gorm:"many2many:associations_table"`
}

It is important to note that two related tables need to be tagged with each other in the same way : Packages []Package json:"packages" gorm:"many2many:xxxx" in Branch struct Branches []Branch json:"branches" gorm:"many2many:xxxx" in Package struct You don't need to create a table of associations, the gorm will automatically create it (associations_table)

And then:

func main() {
    //types.Test()
    dsn := "root:pwd@tcp(127.0.0.1:3306)/test_db?charset=utf8mb4&parseTime=True&loc=Local"

    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    //db.AutoMigrate(&Branch{}, &Package{})
    // create
    package1 := Package{Name: "package_name_example1", Branches: []Branch{{Name: "branch_name_example1"}, {Name: "branch_name_example2"}}}
    package2 := Package{Name: "package_name_example2", Branches: []Branch{{Name: "branch_name_example3"}}}
    db.Create(&package1)
    db.Create(&package2)

    var pkg Package
    pkg.ID = 1
    if err := db.Preload("Branches").First(&pkg).Error; err != nil {
        fmt.Println(err)
    }
    fmt.Println(pkg)

    var pkg2 Package
    if err := db.Preload("Branches").First(&pkg2, 1).Error; err != nil {
        fmt.Println(err)
    }
    fmt.Println(pkg2)
}

Comment From: gg1229505432

I hope this can help you. 🫡

Comment From: gg1229505432

@jinzhu you can close this Issues

Comment From: dwadp

Hmmm, it doesn't seems to solve my issue @gg1229505432 🤔, i've created another example to demonstrate it, just like you said, i didn't manually create the association table and instead use the db.AutoMigrate, and then defined the relations field in both of the struct. The code looks like this:

package main

import (
    "encoding/json"
    "fmt"
    "log"
    "time"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type (
    Branch struct {
        ID             uint      `json:"id"`
        Name           string    `json:"name"`
        CreatedAt      time.Time `json:"created_at"`
        UpdatedAt      time.Time `json:"updated_at"`
        Products       []Product `gorm:"many2many:branch_products;" json:"products"`
        gorm.DeletedAt `json:"deleted_at"`
    }

    Product struct {
        ID             uint      `json:"id"`
        Name           string    `json:"name"`
        Price          float32   `json:"price"`
        Branches       []Branch  `gorm:"many2many:branch_products;" json:"branches"`
        CreatedAt      time.Time `json:"created_at"`
        UpdatedAt      time.Time `json:"updated_at"`
        gorm.DeletedAt `json:"deleted_at"`
    }
)

func main() {
    dsn := "root:root@tcp(127.0.0.1:3306)/gorm_playground?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        log.Fatalf("failed to connect to database: %q\n", err)
    }

    db.AutoMigrate(&Branch{}, &Product{})

    product1 := Product{Name: "product_name_example1", Price: 5, Branches: []Branch{{Name: "branch_name_example1"}, {Name: "branch_name_example2"}}}
    product2 := Product{Name: "product_name_example2", Price: 10, Branches: []Branch{{Name: "branch_name_example3"}}}
    db.Create(&product1)
    db.Create(&product2)

    products := []Product{}

    err = db.Preload("Branches").Find(&products).Error
    if err != nil {
        log.Fatalf("failed to query products: %q\n", err)
    }

    fmt.Println(products)

    b, err := json.Marshal(map[string]interface{}{
        "products": products,
    })
    if err != nil {
        log.Fatalf("failed to marshal products: %q\n", err)
    }

    fmt.Println(string(b))
    log.Println("done!")
}

Here's the full output of the error in my terminal:

2024/04/14 11:37:26 /home/adi/Projects/Go/gorm-playground/main.go:50 Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` IS NULL' at line 1
[0.360ms] [rows:0] SELECT * FROM `branch_products` WHERE `branch_products`.`product_id` IN (1,2) AND `branch_products`.` IS NULL

2024/04/14 11:37:26 /home/adi/Projects/Go/gorm-playground/main.go:50 Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` IS NULL' at line 1
[1.359ms] [rows:2] SELECT * FROM `products` WHERE `products`.`deleted_at` IS NULL
2024/04/14 11:37:26 failed to query products: "Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` IS NULL' at line 1"
exit status 1

There's still a

AND `branch_products`.` IS NULL

at the end of the relation's query

Comment From: gg1229505432

use DeletedAt time.Time json:"deleted_at" instead of gorm.DeletedAt json:"deleted_at". then all problems can be solve. The gorm.DeletedAt has a bug while using 'many2many'

Comment From: dwadp

Thank you, finally it works👌. Looking for the fix about gorm.DeletedAt in the future @jinzhu @gg1229505432 😄

Comment From: gg1229505432

My pleasure, sir

Comment From: gg1229505432

you can close this issues @jinzhu

Comment From: gg1229505432

maybe we all wrong, We should give the variable a name:

type (
    Branch struct {
        ID        uint           `json:"id"`
        Name      string         `json:"name"`
        Products  []Product      `gorm:"many2many:branch_products;" json:"products"`
        DeletedAt gorm.DeletedAt `json:"deleted_at"`
    }

    Product struct {
        ID        uint           `json:"id"`
        Name      string         `json:"name"`
        Branches  []Branch       `gorm:"many2many:branch_products;" json:"branches"`
        DeletedAt gorm.DeletedAt `json:"deleted_at"`
    }
)

You can see that the DeletedAt variable has a name ( DeletedAt gorm.DeletedAt json:"deleted_at" instead of gorm.DeletedAt json:"deleted_at" ), so that it doesn't report an error.

Comment From: dwadp

maybe we all wrong, We should give the variable a name:

`` type ( Branch struct { ID uintjson:"id"Name stringjson:"name"Products []Productgorm:"many2many:branch_products;" json:"products"DeletedAt gorm.DeletedAtjson:"deleted_at"` }

Product struct { ID uint json:"id" Name string json:"name" Branches []Branch gorm:"many2many:branch_products;" json:"branches" DeletedAt gorm.DeletedAt json:"deleted_at" } ) ```

You can see that the DeletedAt variable has a name ( DeletedAt gorm.DeletedAt json:"deleted_at" instead of gorm.DeletedAt json:"deleted_at" ), so that it doesn't report an error.

This also works with the Preload and still get automatic WHERE condition to query undeleted models without doing it manually 👍