FIX: Ensure the top 6 categories are shown in the user summary (PR #12691)

Previously it would pluck 6 categories which the user had posted in, then order them. To select the top 6 categories, we need to perform the ordering in the SQL query before the LIMIT


Isn’t count(*) potentially quite slow on psql? The post table is quite big.

post_count_query is limited to posts from a specific user, so it’s not the entire table. We are already doing a number of .pluck("count(*)") further down in this function, so I don’t think adding it here is going to cause too much issue.

But you are right - it’s probably not ideal for us to be doing these massive queries for every user profile view. The good news is that @xfalcox added some caching a while back, so it doesn’t happen for every view…

I think to make it more efficient we’d need to set up a user_category_stats table with a cache of post/topic count for each category/user pair. Possible, but it would be quite a large refactor.

Ok, sounds good.