Showing User's Post Counts by Custom Post Type in the Admin's User List?

Question

I'm trying to figure out how to hook into the /wp-admin/users.php manage page to create custom columns for showing the number of posts users have for the custom post types on WPHonors.com.

I created a trac ticket for this but @nacin explained why it's a job more for a plugin to do instead.

I have been unable to find a way to manipulate the output of the users table, so I can add custom columns for CPTs post counts for each user. And that may have something to do with the question @nacin asked, what would the post count numbers link to. For the current 'post' post count a user has, it links to the post manage page, showing all posts for that user (/wp-admin/edit.php?author=%author_id%).

If I were to link it somewhere, it would be to:

/wp-admin/edit.php?post_type=%post_type%&author=%author_id%

If that were even somehow possible, I guess. But I don't even necessarily need to link it to anywhere. I mostly want to just show CPT post counts for each person, having 600 users and a combined total of 300+ posts across 4 custom post types. Admins are only one who can submit 'post' posts, so that column in the user's page is useless.

Answer 1

Here's an expansion of Mike's tutorial answer. I added links to the types listed so you can click on one and be taken right to a listing of all the posts in that type for that author, which required an additional variable for $counts and some extra output for $custom_column[]

add_action('manage_users_columns','yoursite_manage_users_columns');
function yoursite_manage_users_columns($column_headers) {
    unset($column_headers['posts']);
    $column_headers['custom_posts'] = 'Assets';
    return $column_headers;
}

add_action('manage_users_custom_column','yoursite_manage_users_custom_column',10,3);
function yoursite_manage_users_custom_column($custom_column,$column_name,$user_id) {
    if ($column_name=='custom_posts') {
        $counts = _yoursite_get_author_post_type_counts();
        $custom_column = array();
        if (isset($counts[$user_id]) && is_array($counts[$user_id]))
            foreach($counts[$user_id] as $count) {
                $link = admin_url() . "edit.php?post_type=" . $count['type']. "&author=".$user_id;
                // admin_url() . "edit.php?author=" . $user->ID;
                $custom_column[] = "\t<tr><th><a href={$link}>{$count['label']}</a></th><td>{$count['count']}</td></tr>";
            }
        $custom_column = implode("\n",$custom_column);
        if (empty($custom_column))
            $custom_column = "<th>[none]</th>";
        $custom_column = "<table>\n{$custom_column}\n</table>";
    }
    return $custom_column;
}

function _yoursite_get_author_post_type_counts() {
    static $counts;
    if (!isset($counts)) {
        global $wpdb;
        global $wp_post_types;
        $sql = <<<SQL
        SELECT
        post_type,
        post_author,
        COUNT(*) AS post_count
        FROM
        {$wpdb->posts}
        WHERE 1=1
        AND post_type NOT IN ('revision','nav_menu_item')
        AND post_status IN ('publish','pending', 'draft')
        GROUP BY
        post_type,
        post_author
SQL;
        $posts = $wpdb->get_results($sql);
        foreach($posts as $post) {
            $post_type_object = $wp_post_types[$post_type = $post->post_type];
            if (!empty($post_type_object->label))
                $label = $post_type_object->label;
            else if (!empty($post_type_object->labels->name))
                $label = $post_type_object->labels->name;
            else
                $label = ucfirst(str_replace(array('-','_'),' ',$post_type));
            if (!isset($counts[$post_author = $post->post_author]))
                $counts[$post_author] = array();
            $counts[$post_author][] = array(
                'label' => $label,
                'count' => $post->post_count,
                'type' => $post->post_type,
                );
        }
    }
    return $counts;
}

Answer 2

Assuming I understood the question, what you need to do is hook into the two hooks related to column headers and columns value for the admin manage pages. They are 'manage_{$type}_columns' and 'manage_{$type}_custom_column' where in your use-case {$type} is users.

The 'manage_users_columns' hook

This first one is simple, it lets you specify the column headers and hence the available columns. WordPress hardcodes the value of the "Posts" column so since you want to change it we are simply going to remove it with unset() and then add a new column with the same title but which instead has the identifier of 'custom_posts':

add_action('manage_users_columns','yoursite_manage_users_columns');
function yoursite_manage_users_columns($column_headers) {
  unset($column_headers['posts']);
  $column_headers['custom_posts'] = 'Posts';
  return $column_headers;
}

The 'manage_users_custom_column' hook

Next you need to use the 'manage_users_custom_column' hook which only gets called for non-standard columns. We test for $column_name=='custom_posts' to make our code robust in case we add new user columns in the future and then we grab the user post type counts from the function I wrote _yoursite_get_author_post_type_counts() which I'll discuss in next. I then played with a few ways to format this but decided an HTML <table> was most appropriate (since it is a table of data). If a table doesn't work for you I assume you'll be able to generate different markup pretty easily:

add_action('manage_users_custom_column','yoursite_manage_users_custom_column',10,3);
function yoursite_manage_users_custom_column($custom_column,$column_name,$user_id) {
  if ($column_name=='custom_posts') {
    $counts = _yoursite_get_author_post_type_counts();
    $custom_column = array();
    if (isset($counts[$user_id]) && is_array($counts[$user_id]))
      foreach($counts[$user_id] as $count)
        $custom_column[] = "\t<tr><th>{$count['label']}</th>" .
                                 "<td>{$count['count']}</td></tr>";
    $custom_column = implode("\n",$custom_column);
  }
  if (empty($custom_column)) 
    $custom_column = "No Posts!";
  else 
    $custom_column = "<table>\n{$custom_column}\n</table>";
  return $custom_column;
}

Getting Posts Counts by Post Type for Each User/Author

Lastly there is the retrieval of post counts by post type by author/user. Generally I try to stick with using WP_Query() when running queries on posts but this query would have required using so many other hooks it just seemed easier to be "naughty" and do all in one.

I omitted any post of $post->post_type is 'revision' or 'nav_menu_item' but left in 'attachments'. You might find it better to explicitly include the post types you want instead of excluding the few I did.

I also filtered by $post->post_status for only 'publish' and 'pending'. If you want to also include 'future', 'private' and/or 'draft' you'll need to make the changes in the code.

For each page load I only call this _yoursite_get_author_post_type_counts() function once and then store into a static variable rather than call for each user. I store in an array indexed by author/user IDs containing an array with Post Type name in the element 'label' and of course the count in a same-named element:

function _yoursite_get_author_post_type_counts() {
  static $counts;
  if (!isset($counts)) {
    global $wpdb;
    global $wp_post_types;
    $sql = <<<SQL
SELECT
  post_type,
  post_author,
  COUNT(*) AS post_count
FROM
  {$wpdb->posts}
WHERE 1=1
  AND post_type NOT IN ('revision','nav_menu_item')
  AND post_status IN ('publish','pending')
GROUP BY
  post_type,
  post_author
SQL;
    $posts = $wpdb->get_results($sql);
    foreach($posts as $post) {
      $post_type_object = $wp_post_types[$post_type = $post->post_type];
      if (!empty($post_type_object->label))
        $label = $post_type_object->label;
      else if (!empty($post_type_object->labels->name))
        $label = $post_type_object->labels->name;
      else
        $label = ucfirst(str_replace(array('-','_'),' ',$post_type));
      if (!isset($counts[$post_author = $post->post_author]))
        $counts[$post_author] = array();
      $counts[$post_author][] = array(
        'label' => $label,
        'count' => $post->post_count,
        );
    }
  }
  return $counts;
}

The Resultant UI

And this is what it looks like applied to my test install of WordPress 3.0.1:

_{(source: mikeschinkel.com)}

Download the Full Code

You can download the full code from Gist:

• wp-post-counts-for-user-by-post-type.php

You can copy this code into your theme's functions.php file or store include the file in a plugin, whichever you choose.

Hope this helps!

Answer 3

The following is a variation on sorich87's answer, as I couldn't get his to work, and I wanted to support multiple types automatically:

function my_manage_users_custom_column($output = '', $column, $user_id) {
    global $wpdb;
    $result = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = '$column' AND post_author = $user_id");
    return '<a href="' . admin_url("edit.php?post_type=$column&author=$user_id") . '">' . $result . '</a>';
}
add_filter('manage_users_custom_column', 'my_manage_users_custom_column', 10, 3);

function my_manage_users_columns($columns) {
    // create columns for each type, make sure to use the post_type slug
    $columns['animals'] = 'Animals Count';
    $columns['plants'] = 'Plants Count';
    $columns['insects'] = 'Insect Count';
    return $columns;
}
add_filter('manage_users_columns', 'my_manage_users_columns');

I read up on get_posts_by_author_sql() and how it's supposed to construct a WHERE statement for you, but the results I got were always "1=0". So I just wrote out the rest of the SQL statement, as get_posts_by_author_sql() is only saving you having to write two bits: the post type and the author:

"SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = 'your_custom_type' AND post_author = $user_id"

This works just as well, and will add as many columns as you want, but each one uses up horizontal space, whereas Mike's tutorial will add a single column for custom post types, and then list them as a table within that row. Same info, different visualization. Mike's is probably better for large amounts of types, as it builds a condensed vertical list (and only displays a count item if not empty), while sorich87's method is good for smaller quantities, as there's only so much horizontal column room available.

Don't forget you can add "post_status=publish" to the query to only return published items, as the example currently return all posts...

Answer 4

The following will add it:

function my_manage_users_custom_column($output = '', $column_name, $user_id) {
    global $wpdb;

    if( $column_name !== 'post_type_count' )
        return;

    $where = get_posts_by_author_sql( 'post_type', true, $user_id );
    $result = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->posts $where" );

    return '<a href="' . admin_url("edit.php?post_type=post_type&author=$user_id") . '" title="Post Type Count">' . $result . '</a>';
}
add_filter('manage_users_custom_column', 'my_manage_users_custom_column', 10, 3);

function my_manage_users_columns($columns) {
    $columns['post_type_count'] = __( 'Post Type', 'textdomain' );

    return $columns;
}
add_filter('manage_users_columns', 'my_manage_users_columns');