CODEWITHSUNDEEP
sqlgogo-gorm
Andrew Baker
Andrew Baker

Reputation: 191

GORM query based on child model in many-to-one relationship

I am using GORM and have a structure like so:

type User struct {
  ID string
  Name string
  Posts []Post `gorm:"foreignKey:UserID"`
}

type Post struct {
  ID string

  Content string

  UserID string
  User *User
}

Now, let's say that Bob, a user, made three posts with message contents:

This would be represented as:

User{
  ID: "1",
  Name: "Bob",
  Posts: []Post{
    {
      ID: "1",
      Content: "Hello",
    },
    {
      ID: "2",
      Content: "Good Morning",
    },
    {
      ID: "3",
      Content: "Goodbye",
    },
  }
}

I want to select Bob from the database, but I'm only given the content of the three messages he has sent. So I thought to do something like this:

user := User{
  Posts: []Post{
    {
      Content: "Hello",
    },
    {
      Content: "Good Morning",
    },
    {
      Content: "Goodbye",
    },
  }
}
db.Preload("Post").Find(&user)

I did this, but it wouldn't fetch the user. Is there any way something like this can be done?

Upvotes: 2

Views: 54

Answers (1)

hamed taghizadeh
hamed taghizadeh

Reputation: 46

You're attempting to retrieve a User based on post content. However, multiple users might have posts with the same content, leading to ambiguity.

Why the Current Approach Doesn’t Work

Filtering posts by content alone doesn't specify which user to retrieve, as multiple users may have identical post content.

Recommended Solutions

1. Query Posts First, Then Retrieve the User

Retrieve posts matching the content, then use the UserID from the post to fetch the user:

var posts []Post
db.Where("content IN ?", []string{"Hello", "Good Morning", "Goodbye"}).Find(&posts)

var user User
for _, post := range posts {
    if post.UserID != "" {
        db.First(&user, post.UserID)  // Fetch the user
        break
    }
}

2. Use a JOIN to Fetch the User Directly

Perform a JOIN between the users and posts tables to retrieve the user:

var user User
db.Joins("JOIN posts ON posts.user_id = users.id").
    Where("posts.content IN ?", []string{"Hello", "Good Morning", "Goodbye"}).
    First(&user)

3. Retrieve All Users with Matching Posts

If multiple users can have posts with the same content, fetch all users with matching posts:

var users []User
db.Joins("JOIN posts ON posts.user_id = users.id").
    Where("posts.content IN ?", []string{"Hello", "Good Morning", "Goodbye"}).
    Find(&users)

Conclusion

To avoid ambiguity, either:

Query posts and use UserID to retrieve the user. Use a JOIN to directly fetch the user based on post content. This approach ensures you retrieve the correct user associated with the post content.

Upvotes: 1

Related Questions