Examples of various table relationships used by gorm – primary and foreign keys -> struct

Examples of various table relationships used by gorm-primary and foreign keys->struct

One-to-many relationship (users and articles)

like:

Boss and employees
Goddess and licking dog
teacher and student
Classes and students
Users and articles
...

Take users and articles as examples

models should be like, Attention! ! : The ID in the User table should be consistent with the UID in Article, and the size and comment should be the same.

package models

typeUser struct {
ID uint
Name string `gorm:"size:8"`
Articles []Article `gorm:"foreignKey:UID"` // List of articles owned by the user
}

type Article struct {
ID uint `gorm:"size:4"`
Title string `gorm:"size:16"`
UID uint // belongs to
User User `gorm:"foreignKey:UID"` // Belongs to
}

Example of joint table

Added

Change

Check

Delete

One-to-one relationship (user to user details)

There are relatively few one-to-one relationships and are generally used for table expansion.

For example, a user table has many fields

Then it can be split into two tables, with commonly used fields in the main table and less commonly used fields in the details table.

Table structure construction

type User struct {
  ID uint
  Name string
  Age int
  Gender bool
  UserInfo UserInfo // User details can be obtained through UserInfo
}

type UserInfo struct {
  UserID uint // foreign key
  ID uint
  Addr string
  Like string
}

Added

Add user details and associate existing users

This scenario is particularly suitable for website registration and subsequent information improvement.

When you first register, you only need to fill in very basic information. This is to add a record to the main table.

After registering, go to the personal center, add an avatar, modify the address…

This is to add a schedule

Delete

Change

DB.Create( & amp;UserInfo{
  UserID: 2,
  Addr: "Nanjing City",
  Like: "eating",
})

Check

Generally, the main table is used to check the appendix.

var user User
DB.Preload("UserInfo").Take( & amp;user)
fmt.Println(user)

Many-to-many relationship (article tag, article tag table)

A many-to-many relationship requires a third table to store the relationship between the two tables.

Assume the table structure is as follows:

type Article struct {
  ID uint
  Title string
  Tags []Tag `gorm:"many2many:article_tags"`
}

type Tag struct {
  ID uint
  Name string
}

type ArticleTag struct {
  ArticleID uint `gorm:"primaryKey"`
  TagID uint `gorm:"primaryKey"`
  CreatedAt time.Time
}

Generate table structure

//Set Article’s Tags table to ArticleTag
DB.SetupJoinTable( & amp;Article{}, "Tags", & amp;ArticleTag{})
// If the tag is to apply Article in reverse, then it must also be added
// DB.SetupJoinTable( & amp;Tag{}, "Articles", & amp;ArticleTag{})
err := DB.AutoMigrate( & amp;Article{}, & amp;Tag{}, & amp;ArticleTag{})
fmt.Println(err)

Operation cases

Give some simple examples

1. Add articles and tags, and automatically associate them

2. Add an article and associate existing tags

3. Associate tags with existing articles

4. Replace tags for existing articles

5. Add articles and tags, and automatically associate them

DB.SetupJoinTable( & amp;Article{}, "Tags", & amp;ArticleTag{}) // To set this, we can go to our custom connection table
DB.Create( & amp;Article{
  Title: "Getting started with flask",
  Tags: []Tag{
    {Name: "python"},
    {Name: "Backend"},
    {Name: "web"},
  },
})
// CreatedAt time.Time Since we set CreatedAt, gorm will automatically fill in the current time.
// If it is other fields, you need to use the Add hook of ArticleTag BeforeCreate

2. Add an article and associate existing tags

DB.SetupJoinTable( & amp;Article{}, "Tags", & amp;ArticleTag{})
var tags []Tag
DB.Find( & amp;tags, "name in ?", []string{"python", "web"})
DB.Create( & amp;Article{
  Title: "flask request object",
  Tags: tags,
})

3. Associate tags with existing articles

DB.SetupJoinTable( & amp;Article{}, "Tags", & amp;ArticleTag{})
article := Article{
  Title: "django basics",
}
DB.Create(&article)
var at Article
var tags []Tag
DB.Find( & amp;tags, "name in ?", []string{"python", "web"})
DB.Take( & amp;at, article.ID).Association("Tags").Append(tags)

4. Replace tags for existing articles

var article Article
var tags []Tag
DB.Find( & amp;tags, "name in ?", []string{"backend"})
DB.Take( & amp;article, "title = ?", "django basics")
DB.Model( & amp;article).Association("Tags").Replace(tags)

5. Query the article list and display tags

var articles []Article
DB.Preload("Tags").Find( & amp;articles)
fmt.Println(articles)

Customize the primary key of the connection table

This function is still very useful. For example, your article table may be called ArticleModel, and your tag table may be called TagModel.

Then according to gorm’s default primary key names, they are ArticleModelID and TagModelID respectively. They are too long and not practical at all.

In this place, the examples given on the official website look a bit confusing, but I have already run through it.

The main thing is to modify these two items

joinForeignKey The primary key id of the connection

JoinReferences associated primary key id

type ArticleModel struct {
  ID uint
  Title string
  Tags []TagModel `gorm:"many2many:article_tags;joinForeignKey:ArticleID;JoinReferences:TagID"`
}

type TagModel struct {
  ID uint
  Name string
  Articles []ArticleModel `gorm:"many2many:article_tags;joinForeignKey:TagID;JoinReferences:ArticleID"`
}

type ArticleTagModel struct {
  ArticleID uint `gorm:"primaryKey"` // article_id
  TagID uint `gorm:"primaryKey"` // tag_id
  CreatedAt time.Time
}

Generate table structure

DB.SetupJoinTable( & amp;ArticleModel{}, "Tags", & amp;ArticleTagModel{})
DB.SetupJoinTable( & amp;TagModel{}, "Articles", & amp;ArticleTagModel{})
err := DB.AutoMigrate( & amp;ArticleModel{}, & amp;TagModel{}, & amp;ArticleTagModel{})
fmt.Println(err)

Add, update, and query operations are the same as above

Operation connection table

If you use a table to operate the connection table, it will be more troublesome.

For example, query which tags are associated with an article

Or to give a more general example, users and articles, when did a user collect which article?

It is not easy to check whether it is related to articles by users or users related to articles.

The simplest thing is to directly check the connection table

type UserModel struct {
  ID uint
  Name string
  Collects []ArticleModel `gorm:"many2many:user_collect_models;joinForeignKey:UserID;JoinReferences:ArticleID"`
}

type ArticleModel struct {
  ID uint
  Title string
  // You can also use reverse references here to check which users have collected them based on the article.
}

// UserCollectModel user collection article table
type UserCollectModel struct {
  UserID uint `gorm:"primaryKey"` // article_id
  ArticleID uint `gorm:"primaryKey"` // tag_id
  CreatedAt time.Time
}

func main() {
  DB.SetupJoinTable( & amp;UserModel{}, "Collects", & amp;UserCollectModel{})
  err := DB.AutoMigrate( & amp;UserModel{}, & amp;ArticleModel{}, & amp;UserCollectModel{})
  fmt.Println(err)
}

A common operation is to check the list of favorite articles based on the user.

var user UserModel
DB.Preload("Collects").Take( & amp;user, "name = ?", "Fengfeng")
fmt.Println(user)

But this is not easy to do paging, and it doesn’t take time to collect articles.

var collects []UserCollectModel
DB.Find( & amp;collects, "user_id = ?", 2)
fmt.Println(collects)

In this way, although the user ID, article ID, and collection time can be found, the search can only be based on the user ID, and the user name, article title, etc. cannot be obtained when returned.

We need to change the table structure, no need to re-migrate, and add some fields

type UserModel struct {
  ID uint
  Name string
  Collects []ArticleModel `gorm:"many2many:user_collect_models;joinForeignKey:UserID;JoinReferences:ArticleID"`
}

type ArticleModel struct {
  ID uint
  Title string
}

// UserCollectModel user collection article table
type UserCollectModel struct {
  UserID uint `gorm:"primaryKey"` // article_id
  UserModel UserModel `gorm:"foreignKey:UserID"`
  ArticleID uint `gorm:"primaryKey"` // tag_id
  ArticleModel ArticleModel `gorm:"foreignKey:ArticleID"`
  CreatedAt time.Time
}

Inquire

var collects []UserCollectModel

var userUserModel
DB.Take( & amp;user, "name = ?", "Fengfeng")
// The reason for using map here is that if it is not found, it will check the 0 value. If it is a struct, it will ignore the zero value and query all
DB.Debug().Preload("UserModel").Preload("ArticleModel").Where(map[string]any{"user_id": user.ID}).Find( & amp;collects )

for _, collect := range collects {
  fmt.Println(collect)
}