Gorm Preload with limit

Your Question

How can I add Limit clause to Association Preload to reduce the number association of rows if many to improve performance.

The document you expected this should be explained

type User struct { ID int
Fullname string
Email string Post []Post }

type Post struct { ID int
Content string
UserID int }

If user's posts are more than a thousand, it would not be wise to preload it all with user, I want to get only the last ten posts.

db.Preload("Post").Find(&user) would fetch all the posts associated with the user.

I want something like db.Preload("Post", "limit:10,DESC").Find(&user).

I only used "limit:10,DESC" to explain my point I know there's nothing like that.

Please I need help with this ASAP.

Thanks

Comment From: a631807682

    DB.Preload("Post", func(tx *gorm.DB) *gorm.DB {
        return tx.Limit(10)
    }).Find(&user)

https://gorm.io/docs/preload.html#Custom-Preloading-SQL

Comment From: Mrbeyond

go DB.Preload("Post", func(tx *gorm.DB) *gorm.DB { return tx.Limit(10) }).Find(&user)

https://gorm.io/docs/preload.html#Custom-Preloading-SQL

This is perfect. Thanks.

Comment From: ElegantSoft

@Mrbeyond This will only get 10 posts for all user or users. is there any solution if I will get many users and want to get only 10 posts for each user

Comment From: Mrbeyond

@ElegantSoft I later faced the same issue.

I created and issue on that as well but no response from anybody yet.

Comment From: ElegantSoft

I found a solution with lateral join but it has bad performance. Finally I decided to set column for last comment and get only one comment text and user name from post table. And when user open post I will request the others. This for your example. In my situation ai have rooms and message and I am storing last message text on room table and get messages when user click on room

Comment From: Mrbeyond

@ElegantSoft Thanks for your feedback. That's creative.

This is a serious issue that go-gorm has to look into.

I remember having similar issue with laravel back then in 2019. I was able to resolve it quickly through nested callback query having similar approach of gorm.DB callback.

Comment From: jinzhu

This is not something could be resolved by GORM w/o causing the N+1 problem or bad performance by using lateral join.

You should think about your data structure, for example, maybe you can add a index to user's posts and only query posts with index <= 10, e.g:

DB.Preload("Post", func(tx *gorm.DB) *gorm.DB {
　 return tx.Where("index < ?", 10)
}).Find(&user)

Comment From: Mrbeyond

@jinzhu Thanks. Much appreciated.

Comment From: ZheruiL

Hi @jinzhu , in sql there's a solution for preloading:

SELECT *
FROM posts
WHERE posts.id IN (select max(id)
                        from posts
                        WHERE posts.user_id IN (1, 2)
                        group by user_id);

But with gorm what I can do is:

func(tx *gorm.DB) *gorm.DB {
    return tx.Where("posts.id in (?)",
        tx.Select("max(id)").Model(&model.Post{}).Group("user_id"),
    )
}

SELECT *
FROM posts
WHERE posts.id IN (select max(id)
                        from posts
--                      WHERE posts.user_id IN (1, 2) <- this line is missing
                        group by user_id);

The problem here is that the where in condition can not be added in the preload subquery as I don't know the ids here. Is there a way to add the where in conditions for the sub query ?