mysql 如何检索与多个表有关系的表的所有记录?

5us2dqdw  于 2023-05-05  发布在  Mysql
关注(0)|答案(1)|浏览(83)

更新:
我猜是TLDR;这是我的表的结构:

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"`
}

get by id API现在工作正常,但是get all API仍然会抛出:
找到struct [github-name]/golang_grpc_server/proto的无效字段。EdgeDeviceOwnership的字段用户:为关系定义有效的外键或实现Valuer/Scanner接口
//UPDATE结束
在修复了我在this question中的上一个问题之后,我正在测试给我带来很多问题的模型/表是否工作正常。不幸的是,它没有。由于某种原因,当我为模型调用“getall”API时,我得到了这个错误。
找到struct [github-name]/golang_grpc_server/proto的无效字段。EdgeDeviceOwnership的字段用户:为关系定义有效的外键或实现Valuer/Scanner接口
这和我之前得到的错误很相似,这就是为什么我创建了上一个问题。仔细看,错误似乎来自我的protobuf。我的protoc命令将生成的文件放在protobuf所在的目录下,这让我很困惑。这些协议生成的结构是否以某种方式导致了问题?我不认为我们需要在gRPC中指定外键关系,对吗?
下面是我用来获取所有EdgeDeviceOwnership记录的代码:

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
}

下面是我的protoc命令,如果它有洞察力的话:
protoc --go_out=.--go_opt=paths=source_relative --go-grpc_out=.--go-grpc_opt=paths=source_relative proto/models.proto
更新:
我正在检查EdgeDeviceOwnership的get by id API,它似乎也有一些问题。它的代码看起来像这样:

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
}

它返回一些东西,但缺少owned_edge_device。我更改了get all API以使用Preload()并预加载相同的字段,但它完全不适用于这些,并抛出了与上面相同的错误。
下面是客户端的相关代码:

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,
    })
})

为了清楚起见,我想,这里是服务器端的相关代码:

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
}

更新:
做了一些更新,使OwnedEdgeDeviceEdgeDevice,因为它看起来像你放在Preload()中的字符串被假定为关系的表名,StatusStatusz,因为它看起来像statusMySQL上的保留字。我还使用了.Joins(),因为本质上EdgeDeviceOwnership只与UserEdgeDevice有一对一的关系,如果我理解正确的话。这是否意味着我必须同时执行.Joins().Preload()?不幸的是,有了那些变化,我仍然只得到我期望得到的一半。
更新:
看起来gorm有一个相当“破坏性”的行为,其中它剔除了具有零值的int字段。使用API,我以前只获取状态字段,它是一个整数字段。作为最后的玛丽,我想我也应该包括ID,看看是否真的返回了一些东西。而且它确实返回了ID!所以我尝试获取状态为1的记录,它也返回了1。所以至少现在“固定”了。我正在使用.Joins()作为该API。但是,不幸的是,对于使用.Joins()的get all API,它仍然会在标题上抛出该错误。

fzwojiic

fzwojiic1#

我做了大量的修改,最终导致了我找到的这个修复。首先,我完全抛弃了EdgeDeviceOwnership,因为我找到了实现多对多关系的正确方法。我只是像这样添加了一个EdgeDevice数组到User

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"`
}

我还更新了我的protobuf和所有依赖它的东西,以反映我当前的数据结构。
我所做的一个主要更改是在服务器端代码上,用于获取所有User记录。以前,这很简单。我认为它只是想得到一些东西。然后我改变了它,以确保一切都转换正确,似乎这是一个更复杂的结构表的正确做法。下面是最后的代码:

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
}

相关问题