在PHP开发中,数据库操作是非常常见的任务。而在数据库操作中,查询多个字段是一种常见的需求。针对这一需求,go-gorm是一款强大的ORM库,可以帮助开发者快速、高效地查询多个字段。在本文中,php小编新一将为大家介绍如何在go-gorm中实现查询多个字段,并给出相应的示例代码,帮助大家轻松掌握这一技巧。无论是初学者还是有一定经验的开发者,本文都能为你提供有价值的帮助和指导。一起来看看吧!
我有以下型号:
<code>type User struct { ID uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"` ... } type Environment struct { ID uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"` UserId uuid.UUID `gorm:"type:uuid" json:"userId"` User User `gorm:"foreignKey:UserId;references:ID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"-"` ... } type Secret struct { ID uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"` UserId uuid.UUID `gorm:"type:uuid" json:"userId"` User User `gorm:"foreignKey:UserId;references:ID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"-"` Environments []Environment `gorm:"many2many:environment_secrets;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"environments"` ... } </code>
创建具有一个或多个环境的机密时,environment_secrets
表会根据有多少个环境共享相同的机密来创建一行或多行:
secret_id | environment_id -------------------------- uuid | uuid
我想做的是查询 secrets
表中的 environments
字段。
我遇到的问题是,虽然 Preload
将数据插入 environments
字段,但在 Find
子句期间它似乎不可用:
<code>var secrets []models.Secret if err := db.Preload("Environments").Find(&secrets, "user_id=? AND ? @> environments.id", userSessionId, environmentId).Error; err != nil { return c.Status(fiber.StatusOK).JSON( fiber.Map{"error": err.Error()}, ) } // ERROR: missing FROM-clause entry for table "environments" (SQLSTATE 42P01) </code>
简而言之,我正在尝试编写此查询:在“secrets”表中,查找拥有这些秘密的匹配 userId,并查看秘密中关联的“environments.id”字段以查找匹配的 UUID特定环境 UUID(它也将由该用户拥有)
。
例如,如果我使用此 92a4c405-f4f7-44d9-92df-76bd8a9ac3a6
用户 UUID 查询 secrets
以检查所有权,并使用此 cff8d599-3822-474d-a980-fb054fb9 进行查询23cc
环境UUID,那么结果输出应该看起来像...
<code>[ { "id": "63f3e041-f6d9-4334-95b4-d850465a588a", "userId": "92a4c405-f4f7-44d9-92df-76bd8a9ac3a6", // field to determine ownership by specific user "environments": [ { "id": "cff8d599-3822-474d-a980-fb054fb923cc", // field to determine a matching environment UUID "userId": "92a4c405-f4f7-44d9-92df-76bd8a9ac3a6", // owned by same user "name": "test1", "createdAt": "2023-08-24T09:27:14.065237-07:00", "updatedAt": "2023-08-24T09:27:14.065237-07:00" }, { "id": "65e30501-3bc9-4fbc-8b87-2f4aa57b461f", // this secret happens to also be shared with another environment, however this data should also be included in the results "userId": "92a4c405-f4f7-44d9-92df-76bd8a9ac3a6", // owned by same user "name": "test2", "createdAt": "2023-08-24T12:50:38.73195-07:00", "updatedAt": "2023-08-24T12:50:38.73195-07:00" } ], "key": "BAZINGA", "value": "JDJhJDEwJHR5VjRWZ3l2VjZIbXJoblhIMU1D", "createdAt": "2023-08-24T12:51:05.999483-07:00", "updatedAt": "2023-08-24T12:51:05.999483-07:00" } ...etc ] </code>
是否有一个 JOIN 查询或者可能是一个原始 SQL 查询,我可以编写以使 environments
行数据在 secrets
中可用以进行查询?
不太漂亮,但是这个原始狗 GORM SQL 查询按预期工作:
SELECT * FROM ( SELECT s.id, s.user_id, s.key, s.value, s.created_at, s.updated_at, jsonb_agg(envs) as environments FROM secrets s JOIN environment_secrets es ON s.id = es.secret_id JOIN environments envs on es.environment_id = envs.id WHERE s.user_id = ? GROUP BY s.id ) r WHERE r.environments @> ?;
查询可以理解为...
将机密聚合为 r
(结果),其中 environments
字段具有:
从 r
(结果)中查找 environments
JSON 数组中的部分参数化 id
。
以及一些使用 go Fiber 的示例 Go 代码:
import ( "time" "github.com/gofiber/fiber/v2" "github.com/google/uuid" "gorm.io/datatypes" ) type SecretResult struct { ID uuid.UUID `json:"id"` UserId uuid.UUID `json:"userId"` Environments datatypes.JSON `json:"environments"` Key string `json:"key"` Value []byte `json:"value"` CreatedAt time.Time `json:"createdAt"` UpdatedAt time.Time `json:"updatedAt"` } func Example(c *fiber.Ctx) error { db := database.ConnectToDB(); userSessionId := c.Locals("userSessionId").(uuid.UUID) parsedEnvId, err := uuid.Parse(c.Params("id")) if err != nil { return c.Status(fiber.StatusBadRequest).JSON( fiber.Map{"error": "You must provide a valid environment id!"}, ) } var secrets []SecretResult if err := db.Raw(` USE SQL QUERY MENTIONED ABOVE `, userSessionId,`[{"id":"`+parsedEnvId.String()+`"}]`), ).Scan(&secrets).Error; err != nil { fmt.Printf("Failed to load secrets with %s: %s", parsedEnvId, err.Error()) return c.Status(fiber.StatusInternalServerError).JSON( fiber.Map{"error": "Failed to locate any secrets with that id."}, ) } return c.Status(fiber.StatusOK).JSON(secrets) }
以上是go-gorm 查询多位字段的详细内容。更多信息请关注PHP中文网其他相关文章!