wordpress query database for per user post count in one query

Question

I have a custom user role let's call it "user-role" that can create only a custom post type lets call it cpt under many categories.

What I'm trying to get is a page that displays 20 users per page(will have pagination) with the fallowing info(it should show only users that have more than 2 posts published):
1)Total number of cpt posts published by this user
2)Total number of posts under category 1 and 2 published by this user

I've tried achieving this using WordPress get_users function to fetch for all authors with "user-role" as their role and then loop throw this found users using count_many_users_posts() gotten the total number of posts by each user and then with a new WP_Query fetch all the posts having the initial author id and beeing in the specified category. with a new foreach throw this lasts posts I was counting the number of posts found per user.

Limiting the solution to 2 users the logic seems to work.

The issue: The script runs too slow, and the number or users are very big (approx 3k) so running 2 loops inside another loop is way too slow for this to work.

What I was thinking was to use a custom query where I should combine a select the users table inner joined with usermeta table for the specified user role to get only the user ids for the needed role and all this query inner joined again with posts table from which to get only publish posts with the post_type = cpt, and count the returned rows.

if possible the above joined with a query that will filter again the results accordingly to term_relationships table to count only posts with category 1,2 (this should be returned to the total posts number)

What I've tried but didn't worked (I'm getting no results back but I really don't think I'm using joins and where clause correctly) is:

SELECT $wpdb->users.id, count(*) FROM $wpdb->users 
INNER JOIN $wpdb->usermeta on $wpdb->users.id = $wpdb->usermeta.user_id 
    WHERE $wpdb->usermeta.meta_key = 'wp_capabilities' 
    AND meta_value RLIKE '[[:<:]]user-role[[:>:]]' 
INNER JOIN $wpdb->posts on $wpdb->users.id = $wpdb->posts.post_author 
    WHERE $wpdb->posts.post_type = "cpt" AND $wpdb->posts.post_status = "publish"
    having count(*)>2

I don't know how close I'm with the above but this is what I could build to count all the posts of a specific role users in only one query, to this I then would like to add the count for the posts in the above categories (I can hardcode the terms ids for the categories so there is no need to get other tables in this ecuation except $wpdb->term_relationships)

so after getting the $ids and count(*) from the above I'm thinking the results should be again inner joined with $wpdb->term_relationships something like this:

INNER JOIN $wpdb->term_relationships on $wpdb->term_relationships.object_id = $wpdb->posts.id 
    WHERE term_taxonomy_id IN (1,2)

This is what I came up with, is there a standard solution to this? Or does the Wordpress database and WordPress functions doesn't cover this at this point for such a large set of data?

M Khalid Junaid M Khalid Junaid · Accepted Answer · 2013-10-10T22:16:35

You can try below query for the count of posts for each author having user level = 10 in custom post type

SELECT COUNT(u.`ID`) AS post_count,p.`ID`,u.`user_nicename`   FROM
`wp_posts` p 
LEFT JOIN  `wp_users` u ON( p.`post_author`=u.`ID`) 
INNER JOIN `wp_usermeta`  um ON (u.`ID` = um.`user_id` AND um.`meta_key`='wp_user_level')    
WHERE p.`post_type`='cpt' AND p.`post_status`='publish' AND um.`meta_value`='10'
GROUP BY u.`ID`
HAVING post_count>2

This query is for count of posts by authors in a specific category

SELECT COUNT(CASE WHEN wtt.`term_id`='3' THEN u.`ID` END) AS cat1_count,
COUNT(CASE WHEN wtt.`term_id`='4' THEN u.`ID` END) AS cat2_count,
p.`ID`,u.`user_nicename`   FROM
 `wp_users` u 
INNER JOIN `wp_usermeta`  um ON (u.`ID` = um.`user_id` AND um.`meta_key`='wp_user_level')
LEFT JOIN `wp_posts` p ON(u.`ID`= p.`post_author`)
LEFT JOIN `wp_term_relationships` wtr ON (p.`ID` = wtr.`object_id`) 
LEFT JOIN `wp_term_taxonomy` wtt ON (wtr.`term_taxonomy_id`=wtt.`term_taxonomy_id`)
WHERE p.`post_type`='cpt' AND p.`post_status`='publish' AND um.`meta_value`='10'
GROUP BY u.`ID`

For dummy category no.s i have use termid 3 and 4 to take count you need to add proper termids in order to count the posts of authors in specified categories

wordpress query database for per user post count in one query

1 Answers