How can I transform a subquery to join in gorm?

I am using GORM and I have these models:

type User struct {
  ID    uint
  UUID  uuid.UUID
  Email string
}

type Profile struct {
  ID     uint
  UUID   uuid.UUID
  Domain string
  UserID uuid.UUID
  User   User `gorm:"references:UUID"`
}

Now I want to find all users that have a profile with domain e.g. example.com.

I already tried some "Join" queries but I did not get it to work. However I managed to get it working by using a subquery:

var users []users

DB.Where(
  "uuid IN (?)",
  DB.Select("user_id").Where("domain = ?", "example.com").Table("profiles")
).Find(&users)

But I don't think this is a pretty elegant way. I think a join would be more straight forward. How do I convert this subquery to a join query?

Thanks!

Try this

DB.Select("u.*").Table("users u").Joins("INNER JOIN profiles p on p.user_id = u.uuid").Where("p.domain = ?", "example.com").Find(&users)

this will result:

SELECT u.* FROM users u INNER JOIN profiles p on p.user_id = u.uuid WHERE p.domain = "example.com"

If you prefer using gorm bulit-in feature instead of raw query join, you can try this:

profile := &Profile{Domain: "example.com"}
user := &User{}

db.Select("User.*").Joins("User").Model(&Profile{}).Where(profile).Find(&user)

If we use gorm Debuging mode like this:

db.Debug().Select("User.*").Joins("User").Model(&Profile{}).Where(profile).Find(&user)

The SQL query log will be like this:

SELECT User.*,`User`.`id` AS `User__id`,`User`.`uuid` AS `User__uuid`,`User`.`email` AS `User__email` FROM `profiles` LEFT JOIN `users` `User` ON `profiles`.`user_id` = `User`.`uuid` WHERE `profiles`.`domain` = 'example.com'