How to retrive the rows with not empty associations in GORM-CodePudding

As an example I have two tables in the DB with One2Many relations.

Consider next piece of code (types are straight from the documentation with little changes):

package main

import (
    "github.com/davecgh/go-spew/spew"
    "github.com/gofrs/uuid"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type Email struct {
    ID      uuid.UUID `gorm:"primary_key;type:char(36);"`
    Address string    `gorm:"unique"`
    Uid     uuid.UUID
}

type User struct {
    ID     uuid.UUID `gorm:"primary_key;type:char(36);"`
    Name   string    `gorm:"unique"`
    Emails []Email   `gorm:"ForeignKey:Uid"`
}

func main() {
    var err error
    var db *gorm.DB
    db, err = gorm.Open(mysql.New(mysql.Config{
        DSN: "root:AsdfgH123@tcp(localhost:3306)/maindb?charset=utf8&parseTime=True&loc=Local",
    }), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    if err != nil {
        panic(err)
    }
    err = db.AutoMigrate(
        &User{},
        &Email{})
    if err != nil {
        panic(err)
    }

    uid1, _ := uuid.NewV7()
    uid2, _ := uuid.NewV7()
    eid1, _ := uuid.NewV7()
    eid2, _ := uuid.NewV7()

    users := []User{
        {Name: "user_1", ID: uid1, Emails: []Email{{ID: eid1, Address: "[email protected]"}}},
        {Name: "user_2", ID: uid2, Emails: []Email{{ID: eid2, Address: "[email protected]"}}},
    }
    db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&users)
    dbUsers := []User{}

    db.Preload("Emails", "address like 'user_1@%'").Find(&dbUsers)

    spew.Dump(dbUsers)
}

And as a result I'm getting this:

([]main.User) (len=2 cap=20) {
 (main.User) {
  ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed,
  Name: (string) (len=6) "user_1",
  Emails: ([]main.Email) (len=1 cap=10) {
   (main.Email) {
    ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-70e6-ad25-c5726212a97c,
    Address: (string) (len=18) "[email protected]",
    Uid: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed
   }
  }
 },
 (main.User) {
  ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-738f-ad46-1c7bb36cf79c,
  Name: (string) (len=6) "user_2",
  Emails: ([]main.Email) (cap=10) {
  }
 }
}

I can filter the results in dbUsers based on the fact that user.Emails is not an empty list. But is it possible to do that purely with GORM? To GORM to only return the users with the email requested in Preload?

CodePudding user response：

I think you should be able to solve this using subqueries. Sorry, I cannot run it against a mysql database, so it is untested, but I guess you'll get the gist:

subQuery := db.Select("uid").Where("address like 'user_1@%'").Table("emails")

db.Where("id IN (?)", subQuery).Find(&dbUsers)