CODEWITHSUNDEEP
gogo-gorm
user4338202
user4338202

Reputation:

GORM Preload by condition in a related table

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.

Upvotes: 3

Views: 13099

Answers (2)

Sơn xê kô
Sơn xê kô

Reputation: 207

You can use this method

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

Upvotes: 7

Grid Maniac
Grid Maniac

Reputation: 51

From my experience the most appropriate way to do that with GORM is by utilizing SubQueries:

topicPosts := []model.TopicPost{}   

DB.GetDB().
    Where("topic_id = ? AND post_id IN (?)", id,
        DB.GetDB().Table("posts").
            Select("id").
            Not("is_private = ? AND user_id != ?", "true", currentUser.ID)).
            SubQuery()).

    Preload("Post").
    Find(&topicPosts)

Upvotes: 5

Related Questions