How to query a model where the embedded value of a relationship equals a specific value?

Question

I have 2 different models (cars & types) who are related to each other (belongs-to relationship), where both have an embedded struct for common data (post). I want to retrieve certain types, but only want to receive the answers where the post value of cars equals a certain value.

Shorty said, based on the model underneath, I want to find all types where cars.post.published equals true.

Models

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 am able to get the Car value in the answer object. If I use the Where() function on the Car struct (i.e. Where(Car{Brand: "Volvo"}) I am able to get the value by brand, but when using Post (i.e. Where(Car{Post: Post{Published: true})) it will just return everything.

Preferably I would like to use the main model I need to query as the base 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 can I achieve such a query without using the raw SQL builder?

Answer 1

I was able to manage your problem in the following way. First, I'm gonna share the code, then I'll cover the 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.

The structs definition

I slightly changed it to handle the scenario. I removed the Car field from the Type struct and added its counterpart in the Car struct definition.

Set up & seed

Then, I set up the DB connection via GORM. I synchronized the models defined in the code with the relations present in the DB. I manually seeded some dummy data just for the sake of the demo.

The reading logic

Then, I run a query to fetch the relevant data. I used the following methods:

1. Debug: used to log the actual SQL statements
2. Model: used to specify which relations we're going to work on
3. Preload: used to load the Type association
4. Where: used to specify the criteria (in our case the filter is on the embedded struct)
5. Find: used to map the result on a variable

Let me know if this helps solve your issue, thanks!