go-gorm queries multi-bit fields

In PHP development, database operations are very common tasks. In database operations, querying multiple fields is a common requirement. In response to this demand, go-gorm is a powerful ORM library that can help developers query multiple fields quickly and efficiently. In this article, PHP editor Xinyi will introduce how to query multiple fields in go-gorm, and give corresponding sample code to help you easily master this technique. Whether you are a beginner or an experienced developer, this article can provide you with valuable help and guidance. Let’s take a look!

Question content

I have the following models:

<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>

When you create a secret with one or more environments, the environment_secrets table creates one or more rows based on how many environments share the same secret:

secret_id | environment_id
--------------------------
uuid      | uuid

What I want to do is query the environments field in the secrets table.

The problem I'm having is that while Preload inserts data into the environments field, it doesn't seem to be available during the Find clause:

<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>

In a nutshell, I'm trying to write this query: In the "secrets" table, look for matching userIds that own these secrets, and look at the associated "environments.id" fields in the secrets to find matching UUIDs The specific environment UUID (which will also be owned by this user) .

For example, if I use this 92a4c405-f4f7-44d9-92df-76bd8a9ac3a6 user UUID query secrets to check ownership, and use this cff8d599-3822-474d- a980-fb054fb9 queries the 23cc environment UUID, then the result output should look like...

<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>

Is there a JOIN query or maybe a raw SQL query that I can write to make the environments rows of data available in secrets for querying?

Workaround

Not pretty, but this raw dog GORM SQL query works as expected:

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 @> ?;

The query can be understood as...

Aggregate secrets into r (the result) where the environments field has:

• Secret ID matching multiple pairs of table secret IDs
• Multi-table environment ID matching the environment ID
• And filter based on the secret user ID matching the parameter user ID

Find the partial parameterized id in the environments JSON array from r (result).

And some example Go code using go Fiber:

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

The above is the detailed content of go-gorm queries multi-bit fields. For more information, please follow other related articles on the PHP Chinese website!