Home > Enterprise >  Relational Database Resulting in Loop
Relational Database Resulting in Loop

Time:04-13

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": "[email protected]",
        "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
            }
        },`

CodePudding user response:

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://docs.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

  • Related