How to query a model where the embedded value of a relation is equal to a specific value?

When developing and designing database models, sometimes we need to query whether the embedded value in the relationship is equal to a specific value. This problem is often encountered in practical applications, but it may not be easy to solve. In this article, I will introduce you to a model with an efficient way to query whether the embedded value of a relationship is equal to a specific value. No need to worry, I will explain it clearly in concise and easy-to-understand language to help you quickly understand and apply it to actual development. Let’s take a look!

Question content

I have two different models (cars and types) that are related to each other (belong to relationships), two of which Models all have an embedded struct for public data (post). I want to retrieve certain types, but only receive answers where the post's cars value is equal to a certain value.

shorty said, based on the model below, I want to find all types where cars.post.published is equal to true.

Model

type post struct {
  published bool
}

type car struct {
  gorm.model
  brand string
  post post `gorm:"embedded"`
}

type type struct {
  gorm.model
  name string
  carid uint32
  car car
  post post `gorm:"embedded"`
}

Using db.preload("car").find(&type) I was able to get the car value in the answer object. If I use the where() function on the car structure (i.e. where(car{brand: "volvo"}) I can pass brand Gets the value, but when using post (i.e. where(car{post: post {published: true})) it just returns everything.

I would be better off using the main model that needs to be queried as the basis for the where() function. For example:

q := Type{Car: Car{Post: Post{Published: true}}}
db.Preload("Car").Where(q).Find(&Type)

...but this doesn't seem to work. How to implement such query without using raw sql generator?

Solution

I was able to solve your problem in the following ways. First I'll share the code and then I'll cover the key points worth explaining.

package main

import (
    "fmt"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Post struct {
    Published bool
}

type Car struct {
    gorm.Model
    Brand  string
    TypeID int
    Type   Type
    Post   Post `gorm:"embedded"`
}

type Type struct {
    gorm.Model
    Name  string
    CarID int
    Post  Post `gorm:"embedded"`
}

func main() {
    dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn))
    if err != nil {
        panic(err)
    }
    db.AutoMigrate(&Car{})
    db.AutoMigrate(&Type{})

    // uncomment these to seed data
    // db.Create(&Car{Brand: "Tesla", Type: Type{Name: "SUV", Post: Post{Published: true}}, Post: Post{Published: true}})
    // db.Create(&Car{Brand: "Ford", Type: Type{Name: "City", Post: Post{Published: false}}, Post: Post{Published: false}})

    var cars []Car
    if err := db.Debug().Model(&Car{}).Preload("Type").Where(&Car{Post: Post{Published: true}}).Find(&cars).Error; err != nil {
        panic(err)
    }
    for _, v := range cars {
        fmt.Println(v.Type.Name)
    }
}

Now, let me share some insights.

Structure definition

I changed it slightly to handle this scenario. I removed the car field from the type structure and added its counterpart in the car structure definition.

Settings and Seeds

Then, I set up the database connection through gorm. I synchronize the model defined in the code with the relationship that exists in the database. To demonstrate, I manually seeded some dummy data.

Read logic

Then, I run the query to get the relevant data. I used the following method:

1. debug: used to record actual sql statements
2. model: used to specify the relationship we want to process
3. preload: used to load typeassociation
4. where: used to specify conditions (in our case, the filter is on the embedded structure)
5. find: used to map results to variables

Please tell me if this helps solve your problem, thank you!

The above is the detailed content of How to query a model where the embedded value of a relation is equal to a specific value?. For more information, please follow other related articles on the PHP Chinese website!