[Rails][Postgres] How to order users by most posts in a category?

Question

I am struggling to achieve this

I have 3 models,

User (has_many :posts)
Post (Belongs_to :user, HABTM :categories)
Category (HABTM :posts)

Now, let's say I want to find and order all users who have submitted most posts in a category, how do I achieve this.

Eg. For category 'Fashion' I want to fetch & order users by number of user's posts in fashion.

Desired result should give,

Mark (7 posts in fashion)
Dave (5 posts in fashion)
Carla (4 posts in fashion) .. so on

Note: Would prefer a solution that is compatible with postgres

score 1 · Accepted Answer · answered Jan 08 '22 at 19:52

First of all, I think the has_and_belongs_to_many relationship for your purpose (or maybe almost any purposes) is inappropriate (or at least inconvenient) in this case. You should use has_many :through instead.

Why? Because has_and_belongs_to_many is not designed to achieve anything other than the bare-minimum basics, like what you want to do. For more in-depth justifications (of why you should use has_many :through for almost any many-to-many relationships), see, for example, "Why You Don't Need Has_and_belongs_to_many Relationships" by Flatiron School and "Create a many-to-many ActiveRecord association in Ruby on Rails with has_many :through and has_and_belongs_to_many" at Development Simplified. For your reference, "Migration path from HABTM to has_many :through" by Christian Rolle may be helpful, which gives a migration guide for it.

Now, suppose you have migrated your model to "has_many :through". Then, in Rails 5+, which supports left_joins, (I think) the following will give the ordered User Relation based on the number of Posts each User has in a specified Category:

User.left_joins(:posts).
     left_joins(posts: :post_category_joins).
     where('post_category_joins.category_id = ?', YOUR_CHOSEN_CATEGORY_ID).
     group(:id).
     order('COUNT(post_category_joins.post_id) DESC')

where post_category_joins is your (chosen) join table name between Post and Category and YOUR_CHOSEN_CATEGORY_ID is the Category ID of your specified Category.

This answer is based on a Stackoverflow answer to a has_many relationship case.

Thanks for your answer, it certainly has pointed me to the right direction. I have made some progress, but still have not achieved the desired result. Please see this question https://stackoverflow.com/q/70638180/6752341 — HighOnRails, Jan 09 '22 at 03:22

[Rails][Postgres] How to order users by most posts in a category?

1 Answers1