How can I transform a subquery to join in gorm?

Question

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!

Answer 1

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"