GORM Preload by condition in a related table

Question

I can't figure how to properly write a query that will select only those entries where condition is applied against relations table. My many-to-many schema looks like this Topics - TopicPosts - Posts. I want to query all TopicPosts where Post is not private or is belongs to the current user. I'm doing it like this:

topicPosts := []model.TopicPost{}
h.DB.
    Where("topic_id = ?", id).
    Preload("Post", func(db *gorm.DB) *gorm.DB {
        return db.Not("is_private = ? AND user_id != ?", "true", currentUser.ID)
    }).
    Preload("Post.Tags").
    Find(&topicPosts)

As expected it returns all TopicPosts but does not eagerly load Posts with a given condition. I then manually filter them out:

publicTopicPosts := []model.TopicPost{}
for _, p := range topicPosts {
    if p.Post.ID != 0 {
        publicTopicPosts = append(publicTopicPosts, p)
    }
}

I realise this is a subpar solution, I'm not very good with SQL but I think it should be possible to achieve within a single query. I would appreciate any help. I'm using Postgres if it matters.

Answer 1

You can use this method

db.Preload("Post", "is_private = ? AND user_id != ?", "true", currentUser.ID).Find(&topicPosts)