关系数据库导致循环

huangapple go评论75阅读模式
英文:

Relational Database Resulting in Loop

问题

我有以下层次结构:用户 -> 地图 -> 元素 -> 帖子
一个用户可以拥有多个地图,每个地图都会有一些元素,每个元素都会有一些帖子。

type User struct {
	UserID    uint   `gorm:"primarykey;autoIncrement;not_null"`
	UserName  string `json:"user_name"`
	FirstName string `json:"first_name"`
	LastName  string `json:"last_name"`
	Email     string `json:"email"`
	Password  []byte `json:"-"`
	Phone     string `json:"phone"`
	Maps      []Map  `gorm:"-"`
}

type Map struct {
	MapID    uint      `gorm:"primarykey;autoIncrement;not_null"`
	UserID   uint      `json:"user_id"`
	User     User      `json:"user" gorm:"foreignkey:UserID"`
	Title    string    `json:"title"`
	Desc     string    `json:"desc"`
	Elements []Element `gorm:"foreignKey:MapID"`
	Date     time.Time `json:"date"`
}

type Element struct {
	ElementID   uint      `gorm:"primarykey;autoIncrement;not_null"`
	ElementName string    `json:"element_name"`
	Desc        string    `json:"desc"`
	MapID       uint      `json:"map_id"`
	Map         Map       `json:"map" gorm:"foreignkey:MapID"`
	Posts       []Post    `gorm:"foreignKey:ElementID"`
	Date        time.Time `json:"date"`
	UserID      uint      `json:"user_id"`
	User        User      `json:"user" gorm:"foreignkey:UserID"`
}

type Post struct {
	PostID    uint      `gorm:"primarykey;autoIncrement;not_null"`
	Title     string    `json:"p_title"`
	Subject   string    `json:"subject"`
	Date      time.Time `json:"date"`
	Entry     string    `json:"entry_text"`
	ElementID uint      `json:"element_id"`
	Element   Element   `json:"element" gorm:"foreignkey:ElementID"`
	UserID    uint      `json:"user_id"`
	User      User      `json:"user" gorm:"foreignkey:UserID"`
}

这些代码看起来都没问题,但是当我从后端发送JSON响应时,可能会出现无限循环的问题。

当我检索用户的所有地图时,它会列出与创建地图的用户相关的用户对象,但是地图还包括元素列表,而在元素对象内部,它将列出它所属的地图,而该地图对象将再次列出其所有元素。

那么,我应该通过只在一个方向上预加载层次结构来处理这个问题吗?

var getmaps []models.Map
database.DB.Preload("User").Preload("Map").Preload("Elements").Offset(offset).Limit(limit).Find(&getmaps)

还是应该修复结构和gorm设置,只获取一个方向上的关系?因为返回一个地图将返回其元素,而每个元素将返回它所属的地图,这样就会循环回到它的元素等等。

这个循环也会发生在元素和帖子之间,其中一个元素将有多个帖子,这些帖子对象将显示它们的元素,而该元素对象将显示其帖子。

我相信有一种理想或最佳的实现方式,所以我想知道人们会推荐什么。

例如,调用带有以下预加载的一个地图:

func DetailMap(c *fiber.Ctx) error {
	id, _ := strconv.Atoi(c.Params("id"))
	fmt.Println(id)
	var smap models.Map
	database.DB.Where("map_id=?", id).Preload("User").Preload("Map").Preload("Elements.Posts").First(&smap)
	return c.JSON(fiber.Map{
		"data": smap,
	})
}
"data": {
    "MapID": 1,
    "UserID": 1,
    "user": {
        "UserID": 1,
        "UserName": "Chris",
        "FirstName": "Chris",
        "LastName": "XxxXxxxx",
        "Email": "xxxxx@gmail.com",
        "phone": "123-456-6789",
        "Maps": null
    },
    "Title": "My Map",
    "Desc": "This is the subject",
    "Elements": [
        {
            "ElementID": 1,
            "ElementType": "BASE",
            "ElementName": "Identity",
            "BriefDesc": "This is the identity ",
            "Desc": "In publishing and graphic design",
            "ParentId": "",
            "NumElements": 0,
            "NumEntries": 0,
            "MapID": 1,
            "map": {
                "MapID": 0,
                "UserID": 0,
                "user": {
                    "UserID": 0,
                    "UserName": "",
                    "FirstName": "",
                    "LastName": "",
                    "Email": "",
                    "phone": "",
                    "Maps": null
                },
                "Title": "",
                "Desc": "",
                "Elements": null,
                "Date": "0001-01-01T00:00:00Z"
            },
            "Notes": null,
            "Questions": null,
            "Posts": [
                {
                    "PostId": 1,
                    "Title": "First Post",
                    "Subject": "This is the subject",
                    "Date": "2022-04-11T12:35:55.267-03:00",
                    "Entry": "This is the Entry",
                    "ElementID": 1,
                    "element": {
                        "ElementID": 0,
                        "ElementType": "",
                        "ElementName": "",
                        "BriefDesc": "",
                        "Desc": "",
                        "ParentId": "",
                        "NumElements": 0,
                        "NumEntries": 0,
                        "MapID": 0,
                        "map": {
                            "MapID": 0,
                            "UserID": 0,
                            "user": {
                                "UserID": 0,
                                "UserName": "",
                                "FirstName": "",
                                "LastName": "",
                                "Email": "",
                                "phone": "",
                                "Maps": null
                            },
                            "Title": "",
                            "Desc": "",
                            "Elements": null,
                            "Date": "0001-01-01T00:00:00Z"
                        },
                        "Notes": null,
                        "Questions": null,
                        "Posts": null,
                        "Date": "0001-01-01T00:00:00Z",
                        "UserID": 0,
                        "user": {
                            "UserID": 0,
                            "UserName": "",
                            "FirstName": "",
                            "LastName": "",
                            "Email": "",
                            "phone": "",
                            "Maps": null
                        }
                    },
                    "UserID": 1,
                    "user": {
                        "UserID": 0,
                        "UserName": "",
                        "FirstName": "",
                        "LastName": "",
                        "Email": "",
                        "phone": "",
                        "Maps": null
                    }
                }
            ],
            "Date": "2022-04-11T11:31:01.72-03:00",
            "UserID": 1,
            "user": {
                "UserID": 0,
                "UserName": "",
                "FirstName": "",
                "LastName": "",
                "Email": "",
                "phone": "",
                "Maps": null
            }
        },
英文:

I have the following hierarchy, Users -> Maps -> Elements -> Posts
A user can have a bunch of maps, each map will have a number of elements and each element will have a number of posts.

type User struct {

UserID        uint   `gorm:"primarykey;autoIncrement;not_null"`
UserName string `json: user_name`
FirstName string `json: first_name`
LastName  string `json: last_name`
Email     string `json:email`
Password  []byte `json:"-"`
Phone     string `json:"phone"`
Maps []Map `gorm:"-"`

}

type Map struct {

MapID   uint      `gorm:"primarykey;autoIncrement;not_null"`
UserID   uint    `json:userid`
User     User      `json:"user"; gorm:"foreignkey:UserID`
Title    string    `json:title`
Desc     string    `json: "desc"`
Elements []Element `gorm:"foreignKey:MapID"`
Date     time.Time `json: date`

}

type Element struct {

ElementID   uint       `gorm:"primarykey;autoIncrement;not_null"`
ElementName string     `json: element_name`
Desc        string     `json: desc`
MapID uint `json:mapid`
Map   Map   `json:"map"; gorm:"foreignkey:MapID`
Posts       []Post     `gorm:"foreignKey:ElementID"`
Date        time.Time  `json: date`
UserID uint `json:userid`
User   User   `json:"user"; gorm:"foreignkey:UserID`

}

type Post struct {

PostId    uint      `gorm:"primarykey;autoIncrement;not_null"`
Title     string    `json: p_title`
Subject   string    `json: subject`
Date      time.Time `json: date`
Entry     string    `json: entry_text`
ElementID uint `json:elementid`
Element   Element   `json:"element"; gorm:"foreignkey:ElementID`
UserID uint `json:userid`
User   User   `json:"user"; gorm:"foreignkey:UserID`

}

This all seems to work fine, but now when I send the JSON response from the backend there seems to be potential for an infinite loop.

When I retrieve all of a user's maps, it then lists the user object relating to the user that created the map, but the map then also includes a list of elements and within the element object it is going to list the map it belongs to and that map object will again list all of it's elements.

So should I be handling this by just preloading the hierarchy in one direction?

var getmaps []models.Map
database.DB.Preload("User").Preload("Map").Preload("Elements").Offset(offset).Limit(limit).Find(&getmaps)

Or should I be fixing the struct and gorm settings to only get relationships in one direction? Since returning a map will return it's elements and each element will return the map it belongs to which loops back to its elements etc.

This loop would also happen with Elements and posts where an element will have many posts, those post objects would display their element and that element object would display its posts.

I'm sure there is an ideal or optimum way to implement this so just curious what people would recommend.

Example calling one map with the following preloads

func DetailMap(c *fiber.Ctx) error {
id,_ := strconv.Atoi(c.Params("id"))
fmt.Println(id)
var smap models.Map
database.DB.Where("map_id=?", id).Preload("User").Preload("Map").Preload("Elements.Posts").First(&smap)
return c.JSON(fiber.Map{
"data":smap,
})
}

"data": {

    "MapID": 1,
"UserID": 1,
"user": {
"UserID": 1,
"UserName": "Chris",
"FirstName": "Chris",
"LastName": "XxxXxxxx",
"Email": "xxxxx@gmail.com",
"phone": "123-456-6789",
"Maps": null
},
"Title": "My Map",
"Desc": "This is the subject",
"Elements": [
{
"ElementID": 1,
"ElementType": "BASE",
"ElementName": "Identity",
"BriefDesc": "This is the identity ",
"Desc": "In publishing and graphic design
"ParentId": "",
"NumElements": 0,
"NumEntries": 0,
"MapID": 1,
"map": {
"MapID": 0,
"UserID": 0,
"user": {
"UserID": 0,
"UserName": "",
"FirstName": "",
"LastName": "",
"Email": "",
"phone": "",
"Maps": null
},
"Title": "",
"Desc": "",
"Elements": null,
"Date": "0001-01-01T00:00:00Z"
},
"Notes": null,
"Questions": null,
"Posts": [
{
"PostId": 1,
"Title": "First Post",
"Subject": "This is the subject",
"Date": "2022-04-11T12:35:55.267-03:00",
"Entry": "This is the Entry",
"ElementID": 1,
"element": {
"ElementID": 0,
"ElementType": "",
"ElementName": "",
"BriefDesc": "",
"Desc": "",
"ParentId": "",
"NumElements": 0,
"NumEntries": 0,
"MapID": 0,
"map": {
"MapID": 0,
"UserID": 0,
"user": {
"UserID": 0,
"UserName": "",
"FirstName": "",
"LastName": "",
"Email": "",
"phone": "",
"Maps": null
},
"Title": "",
"Desc": "",
"Elements": null,
"Date": "0001-01-01T00:00:00Z"
},
"Notes": null,
"Questions": null,
"Posts": null,
"Date": "0001-01-01T00:00:00Z",
"UserID": 0,
"user": {
"UserID": 0,
"UserName": "",
"FirstName": "",
"LastName": "",
"Email": "",
"phone": "",
"Maps": null
}
},
"UserID": 1,
"user": {
"UserID": 0,
"UserName": "",
"FirstName": "",
"LastName": "",
"Email": "",
"phone": "",
"Maps": null
}
}
],
"Date": "2022-04-11T11:31:01.72-03:00",
"UserID": 1,
"user": {
"UserID": 0,
"UserName": "",
"FirstName": "",
"LastName": "",
"Email": "",
"phone": "",
"Maps": null
}
},`

答案1

得分: 1

在你的PostMapElement结构体中,你有以下字段:

UserID uint   `json:userid`
User   User   `json:"user"; gorm:"foreignkey:UserID`

你应该从你的内容结构体中移除User字段,因为你已经有了UserID。在这种情况下,一个"引用"(ID)比包含整个用户对象更合理。客户端可以调用/users/{id}端点并获取更多信息(如果需要的话)。

同时,通过移除User结构体中的Maps []Map(负责你提到的循环),限制User结构体的内容。然后你需要设置像/user/{id}/maps这样的端点,以便客户端可以获取用户的内容。

对于PostElement也是一样的。你可以只存储ID,或者只存储"子"模型的数组。(Map嵌入ElementElement不嵌入Map)。所以要找到一个元素关联的地图,你可以调用端点/maps/{your element's map ID}。对于Element > Post也是一样的。

type Map struct {
    gorm.Model // 这会处理ID字段
    UserID   uint    `json:userid`
    Title    string    `json:title`
    Desc     string    `json: "desc"`
    Elements []Element // gorm会自动处理关系
    Date     time.Time `json: date`
}
type Element struct {
    gorm.Model // 包含ID
    ElementName string     `json: element_name`
    Desc        string     `json: desc`
    MapID uint `json:mapid`
    // Map   Map  ... 这个关系由另一个端点描述 - /elements/{elementId}/map 来获取相关的地图

    Posts       []Post     // gorm会处理这个
    Date        time.Time  `json: date`
    UserID uint `json:userid`
}
type Post struct {
    gorm.Model
    Title     string    `json: p_title`
    Subject   string    `json: subject`
    Date      time.Time `json: date`
    Entry     string    `json: entry_text`
    ElementID uint `json:elementid` // gorm会将其作为外键使用
    UserID uint `json:userid`
}

为了避免循环,你需要在结构体级别上使关系单向,并设置更多的HTTP路由来实现双向关系(参见注释的代码)。

我描述的是一个简单的REST API。Microsoft有一个很好的概述:https://learn.microsoft.com/en-us/azure/architecture/best-practices/api-design#organize-the-api-design-around-resources - 特别是客户/订单关系会对你有兴趣。

在gorm方面,你将使用一对多关联:https://gorm.io/docs/has_many.html

英文:

In your Post, Map and Element structs you have the fields:

UserID uint   `json:userid`
User   User   `json:"user"; gorm:"foreignkey:UserID`

You should remove the User field from your content structs because you already have a UserID. A "reference" (ID) in this case is more sensible than including the whole user object. The client can call a /users/{id} endpoint and find more info if needed.

Also limit the content of the User struct by removing Maps []Map (responsible for the loop you mentioned). You would then need to set up endpoints like /user/{id}/maps so the client can get the user's content.

The same applies for Post and Element. You could go all-out and store only IDs, or you can store an array of only "child" models. (Map embeds Element, Element DOES NOT embed Map). So to find the associated map of an element, you would call endpoint /maps/{your element's map ID}. same for Element > Post

type Map struct {
    gorm.Model // this takes care of the ID field
    UserID   uint    `json:userid`
    Title    string    `json:title`
    Desc     string    `json: "desc"`
    Elements []Element // gorm will handle the relationship automatically
    Date     time.Time `json: date`
}
type Element struct {
gorm.Model // includes ID
ElementName string     `json: element_name`
Desc        string     `json: desc`
MapID uint `json:mapid`
// Map   Map  ... This relationship is described by another endpoint - /elements/{elementId}/map to get the related map

Posts       []Post     // gorm handles this
Date        time.Time  `json: date`
UserID uint `json:userid`
}
type Post struct {
    gorm.Model
    Title     string    `json: p_title`
    Subject   string    `json: subject`
    Date      time.Time `json: date`
    Entry     string    `json: entry_text`
    ElementID uint `json:elementid` // gorm will use this as fk
    UserID uint `json:userid`
}

To avoid loops you will need to make the relationships one-directional at a struct level, and set up more http routes to go in the other direction (see commented code).

What I described is a simple REST api. Microsoft has a nice overview: https://learn.microsoft.com/en-us/azure/architecture/best-practices/api-design#organize-the-api-design-around-resources - specifically the customer/order relationship will interest you.

On the gorm side you would be using one-to-many associations: https://gorm.io/docs/has_many.html

huangapple
  • 本文由 发表于 2022年4月12日 04:38:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/71834031.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定