I welcome everyone. I have two models: User, Post. They have standard connections. I need to make an Active Record request in such a way that I would immediately get a list of all users and the number of his posts. Include doesn't work because it commits all posts to memory. I somehow thought of this solution, but it does not show users who do not have posts at all.

My solution:

User.joins(:posts).select('users.*, count(posts.id) as post_count').group('users.id')

CodePudding user response：

Right now, you are using an INNER JOIN, thus retaining only rows where a User can be joined with a Post.

To get a list of users and a count of their respective posts, you need to use an OUTER JOIN:

User.left_outer_joins(:posts).select('users.*, count(posts.id) as post_count').group('users.id')

CodePudding user response：

Why not loop over all the users you want to and get them by Post.where(id: user.id) Something along that line

CodePudding user response：

I believe the left_outer_join is good advice, but you could also use the includes method, as Rails already provided this clever algorithm.

Under the hood, however, it will act as a left_outer_join in a more thoughtful way.

User.includes(:posts).select('users.*, count(posts.id) as post_count').group('users.id')

Here is a nice post about preload, eager load, includes and joins with the active record if you want to use it as a reference.

With this, I think you should be able to get the post count by users, including users without posts.