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
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.