I have the following models:
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"`
...
}
When creating a secret that has one or many environments, an environment_secrets
table creates one or many individual rows depending on how many environments share the same secret:
secret_id | environment_id
--------------------------
uuid | uuid
What I'm trying to do is query the environments
field within the secrets
table.
The problem I'm running into is that while Preload
inserts data into the environments
field, it doesn't appear to be available during the Find
clause:
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)
In short, I'm trying to write this query: Within the "secrets" table, look for a matching userId that owns these secrets, and look through the associated "environments.id" field within the secrets for UUIDs that match a specific environment UUID (it'll also owned by this user)
.
For example, if I query secrets
with this 92a4c405-f4f7-44d9-92df-76bd8a9ac3a6
user UUID to check for ownership, and also query with this cff8d599-3822-474d-a980-fb054fb923cc
environment UUID, then the resulting output should look something like...
[
{
"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
]
Is there a JOIN query or perhaps a raw SQL query I can write to have the environments
row data available within secrets
for a query?
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 read as...
Aggregate secrets as r
(results) with an environments
field that has:
From the r
(results) look for a partial parameterized id
within the environments
JSON array.
And some sample go code using gofiber:
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)
}