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!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explains Go's package import mechanisms: named imports (e.g., import "fmt") and blank imports (e.g., import _ "fmt"). Named imports make package contents accessible, while blank imports only execute t

This article explains Beego's NewFlash() function for inter-page data transfer in web applications. It focuses on using NewFlash() to display temporary messages (success, error, warning) between controllers, leveraging the session mechanism. Limita

This article details efficient conversion of MySQL query results into Go struct slices. It emphasizes using database/sql's Scan method for optimal performance, avoiding manual parsing. Best practices for struct field mapping using db tags and robus

This article demonstrates creating mocks and stubs in Go for unit testing. It emphasizes using interfaces, provides examples of mock implementations, and discusses best practices like keeping mocks focused and using assertion libraries. The articl

This article explores Go's custom type constraints for generics. It details how interfaces define minimum type requirements for generic functions, improving type safety and code reusability. The article also discusses limitations and best practices

This article details efficient file writing in Go, comparing os.WriteFile (suitable for small files) with os.OpenFile and buffered writes (optimal for large files). It emphasizes robust error handling, using defer, and checking for specific errors.

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

This article explores using tracing tools to analyze Go application execution flow. It discusses manual and automatic instrumentation techniques, comparing tools like Jaeger, Zipkin, and OpenTelemetry, and highlighting effective data visualization
