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.