Get user categories with most posts in it


I am trying to make a query to take first 3 categories with most posts per user, but no luck so far. This is where I am now:

SELECT DISTINCT(terms.term_id) as term_ID,, terms.slug, posts.post_author, t0.count_id

FROM wp_posts as posts
JOIN wp_term_relationships as relationships ON posts.ID = relationships.object_ID
JOIN wp_term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
JOIN wp_terms as terms ON tax.term_id = terms.term_id

SELECT COUNT(*) as count_id, count_terms.term_id
FROM wp_posts as posts_count
JOIN wp_term_relationships as count_relationships ON posts_count.ID = count_relationships.object_ID
JOIN wp_term_taxonomy as count_tax ON count_relationships.term_taxonomy_id = count_tax.term_taxonomy_id
JOIN wp_terms as count_terms ON count_tax.term_id = count_terms.term_id
WHERE count_tax.taxonomy = "category"
AND posts_count.post_status = "publish"
AND posts_count.post_author in (1,2,3)
group by count_terms.term_id

) as t0 on posts.post_author in (1,2,3)

WHERE tax.taxonomy = "category"
AND posts.post_status = "publish"
AND posts.post_author in (1,2,3)

Which returns the users with categories, but with total sum of all the posts in those categories for this users. And I need first 3 categories with most posts per user. Any idea how to do that?

, , , user1952854 7 years 2016-09-24T04:54:05-05:00 0 Answers 74 views 0

Leave an answer