Your Question

Why is there a .. AND "distribution_places"."distribution_id" IN (NULL) added to my join condition? I need the following without that condition as it fails my query.

I have following two models:

type Distribution struct {
    ID          []byte           `gorm:"type:bytea;primaryKey;uniqueIndex;not null"`
    IdentityKey string           `gorm:"type:text;default:'';not null"`
    Description string           `gorm:"type:text"`
    Places      []Place          `gorm:"many2many:distribution_places;"`
}

type Place struct {
    ID uint64 `gorm:"primaryKey;uniqueIndex;not null"`
    Distributions    []Distribution `gorm:"many2many:distribution_places;"`
    IdentityKey string `gorm:"uniqueIndex:places_merchant_identity"`
}

and am attempting to query them using:

var dists []models.Distribution
db.Debug().Model(&models.Distribution{}).
        Where("places.id IN (?)", placeIds).
        Association("Places").
        Find(&dists)

which results in the follwing query: SELECT "places"."id", ... FROM "places" JOIN "distribution_places" ON "distribution_places"."place_id" = "places"."id" AND "distribution_places"."distribution_id" IN (NULL) WHERE places.id IN (...)

The document you expected this should be explained

https://gorm.io/docs/associations.html#Find-Associations

Expected answer

What am I doing wrong? How do I remove that unneeded join condition? Or write my query that uses the association of my relationship of models

Comment From: dorner

PR on go-gorm playground to show failing test: go-gorm/playground#606

Comment From: black-06

dist := Distribution{
    ID: []byte("some-id"),
    Places: []Place{
        {IdentityKey: "foo"},
        {IdentityKey: "bar"},
    },
    IdentityKey: "baz",
}
db.Create(&dist)

var dists []Distribution
db.Model(&dist.Places).Association("Distributions").Find(&dists)

SQL will be:

SELECT `distributions`.`id`,`distributions`.`identity_key`,`distributions`.`description` 
FROM `distributions` 
JOIN `distribution_places` 
ON `distribution_places`.`distribution_id` = `distributions`.`id` 
AND `distribution_places`.`place_id` IN (1,2)

See https://gorm.io/docs/associations.html#Association-Mode example

user is the source model, it must contains primary key

We want query Distribution by Association, so the model should be Place. We want place_id in (1,2), so the model should be []Place (which have id 1 & 2)

var dists []Distribution
db.Model(&[]Place{
    {Model: gorm.Model{ID: 1}},
    {Model: gorm.Model{ID: 2}},
}).Association("Distributions").Find(&dists)