mysqlforeign-keysprotocol-buffersgo-gormgrpc-go

How to retrieve all records of a table that has relations to multiple tables?


UPDATE:

I guess TLDR; this is my struct for the table:

type EdgeDeviceOwnership struct {
    gorm.Model
    ID           string     `gorm:"primarykey; size:40;"`
    UserID       string     `gorm:"size:40; index; not null"`
    User         User       `gorm:"foreignKey:UserID; constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
    EdgeDeviceID string     `gorm:"size:40; index; not null"`
    EdgeDevice   EdgeDevice `gorm:"foreignKey:EdgeDeviceID; constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
    CreatedAt    time.Time  `gorm:"autoCreateTime:false"`
    UpdatedAt    time.Time  `gorm:"autoUpdateTime:false"`
}

The get by id API is working fine now, but the get all API still throws:

invalid field found for struct [github-name]/golang_grpc_server/proto.EdgeDeviceOwnership's field User: define a valid foreign key for relations or implement the Valuer/Scanner interface

//End of UPDATE

After fixing my previous issue in this question, I was testing if the model/table that was giving me a lot of issue is working fine. Unfortunately, it doesn't. For some reason, when I call the "get all" API for the model, I'm getting this error.

invalid field found for struct [github-name]/golang_grpc_server/proto.EdgeDeviceOwnership's field User: define a valid foreign key for relations or implement the Valuer/Scanner interface

Which was quite the similar error I got previously, that's why I created the previous question. Upon looking closely, the error seems to be coming from where my protobuf is. My protoc command puts the generated files on the same directory as the protobuf, which confuses me. Are these protoc-generated structs causing issues somehow? I don't think we need to specify foreign key relations in gRPC, right?

Here's the code I use for getting all the EdgeDeviceOwnership records:

pb "[github-name]/golang_grpc_server/proto"

func (*Server) GetEdgeDeviceOwnerships(ctx context.Context, req *pb.ReadEdgeDeviceOwnershipsRequest) (*pb.ReadEdgeDeviceOwnershipsResponse, error) {
    fmt.Println("Read EdgeDeviceOwnerships")
    edgeDeviceOwnerships := []*pb.EdgeDeviceOwnership{}
    res := DB.Preload(clause.Associations).Find(&edgeDeviceOwnerships)

    if res.RowsAffected == 0 {
        return nil, errors.New("EdgeDeviceOwnership not found")
    }

    return &pb.ReadEdgeDeviceOwnershipsResponse{
        EdgeDeviceOwnerships: edgeDeviceOwnerships,
    }, nil
}

Here's my protoc command, if it would be insightful:

protoc --go_out=. --go_opt=paths=source_relative --go-grpc_out=. --go-grpc_opt=paths=source_relative proto/models.proto

UPDATE:

I'm checking the get by id API for EdgeDeviceOwnership, and it seems to have a few issues as well. The code for it looks like this:

func (*Server) GetEdgeDeviceOwnership(ctx context.Context, req *pb.ReadEdgeDeviceOwnershipRequest) (*pb.ReadEdgeDeviceOwnershipResponse, error) {
    fmt.Println("Read EdgeDeviceOwnership", req.GetId())
    var edgeDeviceOwnership models.EdgeDeviceOwnership
    res := DB.Preload("User").Preload("OwnedEdgeDevice").Find(&edgeDeviceOwnership, "id = ?", req.GetId())

    if res.RowsAffected == 0 {
        return nil, errors.New("EdgeDeviceOwnership not found")
    }

    pbUser := pb.User{
        FirstName: edgeDeviceOwnership.User.FirstName,
        LastName:  edgeDeviceOwnership.User.LastName,
        UserName:  edgeDeviceOwnership.User.UserName,
        Password:  edgeDeviceOwnership.User.Password,
    }

    pbEdgeDevice := pb.EdgeDevice{
        Status: pb.EdgeDeviceStatus(edgeDeviceOwnership.OwnedEdgeDevice.Status),
    }

    return &pb.ReadEdgeDeviceOwnershipResponse{
        EdgeDeviceOwnership: &pb.EdgeDeviceOwnership{
            Id:              edgeDeviceOwnership.ID,
            User:            &pbUser,
            OwnedEdgeDevice: &pbEdgeDevice,
        },
    }, nil
}

Which returns something, but is missing owned_edge_device. I changed the get all API to use Preload() as well and preloading the same field, but it completely doesn't work for those and throws the same error above.

Here's the relevant code of the client side:

ginGine.GET("/edge_device_ownership/:id", func(ctx *gin.Context) {
    id := ctx.Param("id")
    res, err := srvClient.GetEdgeDeviceOwnership(ctx, &pb.ReadEdgeDeviceOwnershipRequest{Id: id})
    if err != nil {
        ctx.JSON(http.StatusNotFound, gin.H{
            "message": err.Error(),
        })
        return
    }
    ctx.JSON(http.StatusOK, gin.H{
        "edge_device_ownership": res.EdgeDeviceOwnership,
    })
})

ginGine.GET("/edge_device_ownerships", func(ctx *gin.Context) {
    res, err := srvClient.GetEdgeDeviceOwnerships(ctx, &pb.ReadEdgeDeviceOwnershipsRequest{})
    if err != nil {
        ctx.JSON(http.StatusBadRequest, gin.H{
            "error": err,
        })
        return
    }
    ctx.JSON(http.StatusOK, gin.H{
        "edge_device_ownerships": res.EdgeDeviceOwnerships,
    })
})

And for clarity I guess, here the relevant code for the server side:

func (*Server) GetEdgeDeviceOwnership(ctx context.Context, req *pb.ReadEdgeDeviceOwnershipRequest) (*pb.ReadEdgeDeviceOwnershipResponse, error) {
    fmt.Println("Read EdgeDeviceOwnership", req.GetId())
    var edgeDeviceOwnership models.EdgeDeviceOwnership
    res := DB.Preload("User").Preload("OwnedEdgeDevice").Find(&edgeDeviceOwnership, "id = ?", req.GetId())

    if res.RowsAffected == 0 {
        return nil, errors.New("EdgeDeviceOwnership not found")
    }

    pbUser := pb.User{
        FirstName: edgeDeviceOwnership.User.FirstName,
        LastName:  edgeDeviceOwnership.User.LastName,
        UserName:  edgeDeviceOwnership.User.UserName,
        Password:  edgeDeviceOwnership.User.Password,
    }

    pbEdgeDevice := pb.EdgeDevice{
        Status: pb.EdgeDeviceStatus(edgeDeviceOwnership.OwnedEdgeDevice.Status),
    }

    return &pb.ReadEdgeDeviceOwnershipResponse{
        EdgeDeviceOwnership: &pb.EdgeDeviceOwnership{
            Id:              edgeDeviceOwnership.ID,
            User:            &pbUser,
            OwnedEdgeDevice: &pbEdgeDevice,
        },
    }, nil
}

func (*Server) GetEdgeDeviceOwnerships(ctx context.Context, req *pb.ReadEdgeDeviceOwnershipsRequest) (*pb.ReadEdgeDeviceOwnershipsResponse, error) {
    fmt.Println("Read EdgeDeviceOwnerships")
    edgeDeviceOwnerships := []*pb.EdgeDeviceOwnership{}
    res := DB.Preload("User").Preload("OwnedEdgeDevice").Find(&edgeDeviceOwnerships)

    if res.RowsAffected == 0 {
        return nil, errors.New("EdgeDeviceOwnership not found")
    }

    return &pb.ReadEdgeDeviceOwnershipsResponse{
        EdgeDeviceOwnerships: edgeDeviceOwnerships,
    }, nil
}

UPDATE:

Did some updates to make OwnedEdgeDevice to EdgeDevice because it seems like the string you put inside Preload() is assumed as the table name for the relation and Status to Statusz as it seems like status was a reserved word on MySQL. I also used .Joins() this time as essentially EdgeDeviceOwnership only have one-to-one relation with both User and EdgeDevice, if I understand the explanation here correctly. Does that actually mean I have to do .Joins() and .Preload() at the same time? Unfortunately, with those changes, I still only get half of what I expected to get.

UPDATE:

Seems like gorm have a quite "destructive" behavior, wherein it culls int fields that have a zero value. With the get by id API, I was previously only getting the status field, which is an integer field. As a last hail mary, I thought I'd include the ID as well to see if really returns something. And it did return the ID! So I tried to get a record with a 1 status, and it returned that as well. So at least that's "fixed" now. I'm using .Joins() for that API. But, unfortunately, for the get all API, which is using .Joins() as well, it still throws that error on the title.


Solution

  • There was tons of changes I made to eventually lead to this fix that I found. First, I ditched EdgeDeviceOwnership altogether as I found the correct way to implement many-to-many relationship. I just added an array of EdgeDevice to User like so.

    type User struct {
        gorm.Model
        ID               string `gorm:"primarykey; size:40;"`
        FirstName        string
        LastName         string
        UserName         string
        Password         string       `gorm:"column:paszword"`
        OwnedEdgeDevices []EdgeDevice `gorm:"many2many:user_owned_edge_devices;"`
        CreatedAt        time.Time    `gorm:"autoCreateTime:false"`
        UpdatedAt        time.Time    `gorm:"autoUpdateTime:false"`
    }
    

    I also updated my protobuf and everything that rely on it to reflect my current data structure.

    One major change I made is on the server side code for getting all User records. Previously, it was quite simple. I think it pretty much is just trying to get something. Then I changed it to make sure everything is converted properly, and seems like that was the right thing to do for a table with a more complex structure. Here's the final code for that:

    func (*Server) GetUsers(ctx context.Context, req *pb.ReadUsersRequest) (*pb.ReadUsersResponse, error) {
        fmt.Println("Read Users")
        users := []models.User{}
        res := DB.Model(models.User{}).Preload("OwnedEdgeDevices").Find(&users)
    
        if res.RowsAffected == 0 {
            return nil, errors.New("user not found")
        }
    
        resUsers := []*pb.User{}
        for _, el := range users {
            edgDevs := []*pb.EdgeDevice{}
            for _, el2 := range el.OwnedEdgeDevices {
                edgDev := &pb.EdgeDevice{
                    Id:      el2.ID,
                    Statusz: pb.EdgeDeviceStatus(el2.Statusz),
                }
                edgDevs = append(edgDevs, edgDev)
            }
    
            usr := &pb.User{
                Id:               el.ID,
                FirstName:        el.FirstName,
                LastName:         el.LastName,
                UserName:         el.UserName,
                Password:         el.Password,
                OwnedEdgeDevices: edgDevs,
            }
    
            resUsers = append(resUsers, usr)
        }
    
        return &pb.ReadUsersResponse{
            Users: resUsers,
        }, nil
    }