Gorm invalid field found for struct

My database structure goal

one user can join many communities
and
one user can own a community

This is the structure that I came up with, is this correct?

type User struct {
    gorm.Model
	Communities []Community `gorm:"many2many:user_communities" json:"communities"`
	FirstName string `json:"firstName"`
	LastName string `json:"lastName"`
}

type Community struct {
    gorm.Model
    Name string `json:"name"`
	UserID uint `json:"userID"`
	Users []User `gorm:"many2many:user_communities" json:"users"`
}

This is my database setup

func ConnectMysql() {
	// Connect to Mysql database based on the environment variable
	dsn := os.Getenv("MYSQL_DSN")

	database, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

	if err != nil {
		panic("Failed to connect to the database")
	}

	database.AutoMigrate(&models.User{})
	database.AutoMigrate(&models.Community{})

	DB = database
}

and how do I query to check whether a user belongs to a community?

So far I came up with this but it's not working, probably my schema above is wrong

func CreateQuestion() {
    var community Community
    var foundUser User

    communityID = 1
    userID = 1

    // Find the community
    if err := database.Mysql.Where("id = ?", communityID).First(&community).Error; err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Community does not exist"})
		return
	}
    
    // Im not sure whether this query is to check whether the user belongs to the community with id 1 or not.
	if err := database.Mysql.Preload("Community").Where("id = ?", userID).Find(&foundUser).Error; err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "user is not in the community"})
		return
	}
}

The error that I got

> [error] invalid field found for struct
> github.com/testing-be/Community's field Users: define a valid foreign
> key for relations or implement the Valuer/Scanner interface

The reason you got this error is that gorm cannot find the foreign key for the defined relationship.

Add the ID field to both User and Community structs (or use gorm.Model instead of the ID). Also, add many2many for the Communities field as well.

type User struct {
    ID uint `json:"id"`
    Communities []Community `gorm:"many2many:user_communities" json:"communities"`
    FirstName string `json:"firstName"`
    LastName string `json:"lastName"`
}

type Community struct {
    ID uint `json:"id"`
    Name string `json:"name"`
    UserID uint `json:"userID"`
    Users []User `gorm:"many2many:user_communities" json:"users"`
}

By default, gorm will try to resolve the relationship like this:

// Join Table: user_communities
//   foreign key: user_id, reference: users.id
//   foreign key: community_id, reference: communities.id

If your foreign keys in the user_communities table are named differently, here is how you could handle that.

Regarding the Preload function, it takes field names, not field types, so the code should be something like this:

var foundUser User
    if err := database.Mysql.Preload("Communities").Where("id = ?", userID).First(&foundUser).Error; err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "user is not in the community"})
        return
    }

This code loads all the communities that have a relationship with the found user.

EDIT:

The following examples should help you to determine if a specific user belongs to a specific community:

//Raw query
userID := 1
communityID := 1
var found sql.NullBool
if err := database.Mysql.Raw("SELECT 1 FROM users u JOIN user_communities uc ON u.id = uc.user_id WHERE u.id = ? AND uc.community_id = ?", userID, communityID).Scan(&found).Error; err != nil {
      c.JSON(http.StatusBadRequest, gin.H{"error": "error happened"})
            return
    }
if found.Valid && found.Bool {
  //user is part of the specified community
}

//With gorm functions
userID := 1
communityID := 1
var found sql.NullBool
if err := database.Mysql.Table("users u").Joins("JOIN user_communities uc ON u.id = uc.user_id").Where("u.id = ? AND uc.community_id = ?", userID, communityID).Select("1").Scan(&found).Error; err != nil {
      c.JSON(http.StatusBadRequest, gin.H{"error": "error happened"})
            return
    }
if found.Valid && found.Bool {
  //user is part of the specified community
}

Next, the examples on how to load a specified user with a specified community:

//Finds just the specified user and loads just a specified community
userID := 1
communityID := 1
var user User
err := database.Mysql.Preload("Communities", "id = ?", communityID).Joins("JOIN user_communities uc ON u.id = uc.user_id").Where("uc.community_id = ?", communityID).First(&user, userID).Error
// err could be gorm.ErrorNotFound, which means that a record is not found, so this needs to be checked before err != nil check

//Finds just the specified user, loads all user's communities, but makes sure that a user belongs to a specific community
userID := 1
communityID := 1
var user User
err := database.Mysql.Preload("Communities").Joins("JOIN user_communities uc ON u.id = uc.user_id").Where("uc.community_id = ?", communityID).First(&user, userID).Error
// err could be gorm.ErrorNotFound, which means that a record is not found, so this needs to be checked before err != nil check

Perhaps you might have missed gorm.Model in your structs?

type User struct {
    gorm.Model
    Communities []Community `json:"communities"`
    FirstName string `json:"firstName"`
    LastName string `json:"lastName"`
}

type Community struct {
    gorm.Model
    Name string `json:"name"`
    UserID uint `json:"userID"`
    Users []User `gorm:"many2many:user_communities" json:"users"`
}

https://gorm.io/docs/many_to_many.html