[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

Answer 1

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.