10

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.

4 Answers 4

14

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;
}
0
12

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:

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!

7
  • Well, thats easy. All you had to do was say it uses the 'manage_{$type}_columns' and 'manage_{$type}_custom_column' where $type = users, and I could've figured out the rest from there. I had a feeling it did, but I checked it and didn't see users. The rest is easy enough. I appreciate the extensive effort you put into it tho, and I'll be voting for you on WPHonors for sure (since I already have) goo.gl/CrSi Thanks alot :D
    – jaredwilli
    Oct 24, 2010 at 7:31
  • 1
    @jaredwilli - Yes of course. But WordPress Answers goal is to provide answers for people well beyond the first person who asks. That's why I write in-depth even though you might only need to bits of info others might be completely new to the approach. Trying to help both. Oh, and thanks for the nice comments on the site (and chance I could change that photo though? :) Oct 24, 2010 at 7:46
  • Yeah that's why I didn't stop you from just telling me the hook I needed to use. I know I won't be the only one to find a need for this so it's all good.
    – jaredwilli
    Oct 24, 2010 at 7:48
  • Oh sorry, of course I will. I was distracted by a custom post type Im making for a shop site Im building. I don't suppose you figured out a way to link the post counts to a edit.php page showing an posts for the authors of them? Probably need to build that into my CPT Im guessing.
    – jaredwilli
    Oct 24, 2010 at 9:22
  • @jaredwilli - Ah, yes, but looks like @somatic did it for you, right? Oct 24, 2010 at 18:24
2

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

1
  • Great! get_posts_by_author_sql( $column, true, $user_id ); should construct the where statement.
    – sorich87
    Oct 24, 2010 at 19:01
1

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');
6
  • @sorich87 - get_posts_by_author_sql() eh? That one is new to me; thanks! But I just checked your code and I don't think it does what he is expecting. Your get_posts_by_author_sql() call always returns '1=0', and he wanted to get a list of counts by post type for a user; unless I misunderstand this code doesn't do that. Maybe you can fix it? Oct 24, 2010 at 7:57
  • Yes, I misunderstood the question. My code will only add one column for one custom post type. Just replace post_type by the post type name. E.g.: get_posts_by_author_sql( 'book', true, $user_id ); for a post type called 'book'. Tested and it works.
    – sorich87
    Oct 24, 2010 at 8:45
  • P.S.: Also voted for you on WPHonors. You definitely deserve it!
    – sorich87
    Oct 24, 2010 at 8:51
  • I have yet to test this out, but it looks like it would work, just might not have all the functionality Im looking for, but thats easy to add still. Thanks :)
    – jaredwilli
    Oct 24, 2010 at 9:24
  • @sorich87 - Awesome! Oct 24, 2010 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.