Table of Contents
Question content
Workaround
Home Backend Development Golang go-gorm queries multi-bit fields

go-gorm queries multi-bit fields

Feb 09, 2024 pm 08:03 PM

go-gorm 查询多位字段

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>
Copy after login

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
Copy after login

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>
Copy after login

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>
Copy after login

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 @> ?;
Copy after login

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)
}
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What are the vulnerabilities of Debian OpenSSL What are the vulnerabilities of Debian OpenSSL Apr 02, 2025 am 07:30 AM

OpenSSL, as an open source library widely used in secure communications, provides encryption algorithms, keys and certificate management functions. However, there are some known security vulnerabilities in its historical version, some of which are extremely harmful. This article will focus on common vulnerabilities and response measures for OpenSSL in Debian systems. DebianOpenSSL known vulnerabilities: OpenSSL has experienced several serious vulnerabilities, such as: Heart Bleeding Vulnerability (CVE-2014-0160): This vulnerability affects OpenSSL 1.0.1 to 1.0.1f and 1.0.2 to 1.0.2 beta versions. An attacker can use this vulnerability to unauthorized read sensitive information on the server, including encryption keys, etc.

How do you use the pprof tool to analyze Go performance? How do you use the pprof tool to analyze Go performance? Mar 21, 2025 pm 06:37 PM

The article explains how to use the pprof tool for analyzing Go performance, including enabling profiling, collecting data, and identifying common bottlenecks like CPU and memory issues.Character count: 159

How do you write unit tests in Go? How do you write unit tests in Go? Mar 21, 2025 pm 06:34 PM

The article discusses writing unit tests in Go, covering best practices, mocking techniques, and tools for efficient test management.

What is the problem with Queue thread in Go's crawler Colly? What is the problem with Queue thread in Go's crawler Colly? Apr 02, 2025 pm 02:09 PM

Queue threading problem in Go crawler Colly explores the problem of using the Colly crawler library in Go language, developers often encounter problems with threads and request queues. �...

What libraries are used for floating point number operations in Go? What libraries are used for floating point number operations in Go? Apr 02, 2025 pm 02:06 PM

The library used for floating-point number operation in Go language introduces how to ensure the accuracy is...

What is the go fmt command and why is it important? What is the go fmt command and why is it important? Mar 20, 2025 pm 04:21 PM

The article discusses the go fmt command in Go programming, which formats code to adhere to official style guidelines. It highlights the importance of go fmt for maintaining code consistency, readability, and reducing style debates. Best practices fo

PostgreSQL monitoring method under Debian PostgreSQL monitoring method under Debian Apr 02, 2025 am 07:27 AM

This article introduces a variety of methods and tools to monitor PostgreSQL databases under the Debian system, helping you to fully grasp database performance monitoring. 1. Use PostgreSQL to build-in monitoring view PostgreSQL itself provides multiple views for monitoring database activities: pg_stat_activity: displays database activities in real time, including connections, queries, transactions and other information. pg_stat_replication: Monitors replication status, especially suitable for stream replication clusters. pg_stat_database: Provides database statistics, such as database size, transaction commit/rollback times and other key indicators. 2. Use log analysis tool pgBadg

Transforming from front-end to back-end development, is it more promising to learn Java or Golang? Transforming from front-end to back-end development, is it more promising to learn Java or Golang? Apr 02, 2025 am 09:12 AM

Backend learning path: The exploration journey from front-end to back-end As a back-end beginner who transforms from front-end development, you already have the foundation of nodejs,...

See all articles