Gorm project practice-using gen and defining relationships between tables

gorm project practice

ER diagram

Image1

Relationship arrangement

  1. One-to-one relationship:

    • User and UserLog: One user corresponds to one user log, and a one-to-one relationship is established through the primary key of the User model and the foreign key of the UserLog model.

  2. One-to-many relationship:

    • User and Teacher: One user can correspond to multiple teachers, through the foreign key (UserID) of the Teacher model and User The primary key of the model establishes a one-to-many relationship.
    • User and Student: One user can correspond to multiple students, through the foreign key (UserID) of the Student model and User The primary key of the model establishes a one-to-many relationship.
    • Teacher and Class: A teacher can have multiple classes through the foreign key of the Class model (TeacherID) and Teacher The primary key of the model establishes a one-to-many relationship.
    • Student and StudentClass: A student can have multiple classes, through the foreign key of the StudentClass model (StudentID) and Student The primary key of the model establishes a one-to-many relationship.
    • Student and Attendance: A student can have multiple attendance records, through the foreign key (StudentID) of the Attendance model and Student The primary key of the model establishes a one-to-many relationship.

  3. Many-to-many relationship:

    • Student and Class: A student can belong to multiple classes, and a class can have multiple students. The StudentClass model is used as an intermediate table to establish a many-to-many relationship.
    • Teacher and Class: A teacher can teach multiple classes, and a class can have multiple teachers, through the foreign key of the Class model (TeacherID) Establish a many-to-many relationship with the primary key of the Teacher model.

  4. One-to-many reverse relationship:

    • Parent and Student: A parent can have multiple children, through the foreign key of the Student model (StudentID) and Parent The primary key of the model establishes a one-to-many reverse relationship.

Use gen to automatically generate code

First use the gen tool to generate code

package main

// gorm gen configure

import (
    "fmt"

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

    "gorm.io/gen"
)

const MySQLDSN = "root:root@tcp(127.0.0.1:3306)/school?charset=utf8mb4 & amp;parseTime=True"

func connectDB(dsn string) *gorm.DB {
    db, err := gorm.Open(mysql.Open(dsn))
    if err != nil {
        panic(fmt.Errorf("connect db fail: %w", err))
    }
    return db
}

func main() {
    // Specify the specific relative directory (relative to the current file) to generate the code. The default is: ./query
    // By default, code that needs to be queried using WithContext is generated, but this mode can be disabled by setting gen.WithoutContext
    g := gen.NewGenerator(gen.Config{
        // By default, CRUD code will be generated in the OutPath directory, and the model package will be generated in the same directory.
        // Therefore, the final package of OutPath cannot be set to model, which will cause conflicts when there is database table synchronization.
        // If you must use it, you can specify the name of the model package separately through ModelPkgPath.
        OutPath: "dao/query",
        /* ModelPkgPath: "dal/model"*/

        // gen.WithoutContext: disable WithContext mode
        // gen.WithDefaultQuery: Generate a global Query object Q
        // gen.WithQueryInterface: Generate Query interface
        Mode: gen.WithDefaultQuery | gen.WithQueryInterface,
    })

    // Usually reuse the existing SQL connection configuration db (*gorm.DB) in the project
    // Not required, but must be set if you need to reuse gorm.Config during the connection or need to connect to the database to synchronize table information
    g.UseDB(connectDB(MySQLDSN))

    // Generate Model structures and CRUD codes for all tables from the connected database
    // You can also manually specify the data table that needs to generate code
    g.ApplyBasic(g.GenerateAllTable()...)

    //Execute and generate code
    g.Execute()
}

Define foreign key relationships in the model generated by gen

The generated model code is in dao/model

Image2

We need to define foreign key relationships in these Models. First define the relationships between the User table, Teacher table and Student table.

Image3

One-to-one relationship

First, determine the main table and supplementary table. The main table

  • Main table: User, gradually: UserID

  • Attached table: Student, foreign key: UserID

  • Attached table: Teacher, foreign key: UserID

  • Add two in model.User

Image4

  • Change generate, we need to use the changed model to generate query

  •  g.ApplyBasic(
        model.Student{},
        model.Teacher{},
        model.User{},
        model.UserLog{},
        model.Class{},
        model.Course{},
        model.Attendance{},
        model.StudentClass{},
        model.Parent{},
    )
    //Execute and generate code
    g.Execute()

Add user business logic

Here press UserType to create corresponding students and teachers.

func CreateUser(c *gin.Context) {
    var req request.CreateUserRequest
    if err := c.ShouldBindJSON( & amp;req); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"code": http.StatusBadRequest, "error": "Failed to create user request, invalid request parameters"})
        return
    }

    var user model.User

    switch req.UserType {
    case "Student":
        user = model.User{
            Username: req.Username,
            Password: req.Password,
            OpenID: req.OpenID,
            Avatar: req.Avatar,
            LastLogin: time.Now(),
            UserType: req.UserType,
            IsValid: req.IsValid,
            Student: model.Student{
                StudentName: req.Username,
            },
        }
    case "Teacher":
        user = model.User{
            Username: req.Username,
            Password: req.Password,
            OpenID: req.OpenID,
            Avatar: req.Avatar,
            LastLogin: time.Now(),
            UserType: req.UserType,
            IsValid: req.IsValid,
            Teacher: model.Teacher{
                TeacherName: req.Username,
            },
        }
    default:
        c.JSON(http.StatusBadRequest, gin.H{"code": http.StatusBadRequest, "error": "Invalid user type"})
        return
    }

    err := query.User.WithContext(context.Background()).Create( & amp;user)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"code": http.StatusInternalServerError, "error": fmt.Sprintf("Create user request failed, unable to create user: %v", err) })
        return
    }

    c.JSON(http.StatusOK, gin.H{"code": http.StatusOK, "message": "User created successfully"})
}

Image5

Delete user business logic

// DeleteUser function to handle delete user request
func DeleteUser(c *gin.Context) {
userIDStr := c.Param("id") // Assuming the route has "id" parameter

varUser model.User
userID, err := strconv.ParseInt(userIDStr, 10, 64)
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"code": http.StatusBadRequest, "error": "Failed to delete user, invalid user ID"})
return
}
//var user model.User
config.GVA_DB.Take( & amp;User, userID)
ret := config.GVA_DB.Select("Student").Delete( & amp;User)
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"code": http.StatusInternalServerError, "error": fmt.Sprintf("Failed to delete user, unable to delete user: %v", err )})
return
}

c.JSON(http.StatusOK, gin.H{"code": http.StatusOK, "message": fmt.Sprintf("User deleted successfully, RowsAffected: %v", ret.RowsAffected)} )
}

Image6

Update

Since this is the main table, logically just update the table and write the logic to change the corresponding table later.

Mainly to change the avatar and so on



Search


// GetUser handles the function of obtaining a single user request
func GetUser(c *gin.Context) {
userIDStr := c.Param("id") // Assuming the route has "id" parameter

u := query.User
userID, err := strconv.ParseInt(userIDStr, 10, 64)
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"code": http.StatusBadRequest, "error": "Failed to get user, invalid user ID"})
return
}

user, err := query.User.WithContext(context.Background()).Where(query.User.UserID.Eq(int32(userID))).Preload(u.Student, u.Teacher).First()

if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"code": http.StatusInternalServerError, "error": fmt.Sprintf("Failed to get user, unable to get user: %v", err )})
return
}

c.JSON(http.StatusOK, gin.H{"code": http.StatusOK, "user": user})
}

// GetAllUsers handles the function of getting all user requests
func GetAllUsers(c *gin.Context) {
u := query.User
users, err := query.User.WithContext(context.Background()).Preload(u.Student, u.Teacher).Find()
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"code": http.StatusInternalServerError, "error": fmt.Sprintf("Failed to get all users, unable to get user list: %v" , err)})
return
}

c.JSON(http.StatusOK, gin.H{"code": http.StatusOK, "users": users})
}