Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I found a great piece of code here that returns the ID's of users by role. What I would like to do is modify it so it only returns the ID's of the users who have at least one post. I have tried to INNER JOIN the $wpdb->posts table and feel that I did that correctly but not sure. Here is what I have so far:

function getUsersByRole( $roles ) {
    global $wpdb;
    if ( ! is_array( $roles ) ) {
        $roles = explode( ",", $roles );
        array_walk( $roles, 'trim' );
    }
    $sql = '
        SELECT  ID, display_name
        FROM ' . $wpdb->users . ' INNER JOIN ' . $wpdb->usermeta . ' ON ' . $wpdb->users . '.ID=' . $wpdb->usermeta . '.user_id
        AND INNER JOIN '. $wpdb->posts .'  ON ' .$wpdb->users . '.ID=' . $wpdb->posts . '.post_author
        WHERE   ' . $wpdb->usermeta . '.meta_key        =       \'' . $wpdb->prefix . 'capabilities\' 
        AND     (
    ';
    $i = 1;
    foreach ( $roles as $role ) {
        $sql .= ' ' . $wpdb->usermeta . '.meta_value    LIKE    \'%"' . $role . '"%\' ';
        if ( $i < count( $roles ) ) $sql .= ' OR ';
        $i++;
    }
    $sql .= ' ) ';
    $sql .= ' ORDER BY display_name ';
    $userIDs = $wpdb->get_col( $sql );
    return $userIDs;
}

Which the SQL query outputs

SELECT ID, display_name FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID=ba_usermeta.user_id AND INNER JOIN wp_posts ON wp_users.ID=ba_posts.post_author WHERE wp_usermeta.meta_key = 'wp_capabilities' AND ( wp_usermeta.meta_value LIKE '%"author"%' OR wp_usermeta.meta_value LIKE '%"editor"%' ) ORDER BY display_name

I've never used INNER JOIN (or JOIN) so I could doing it wrong. I also think I need to do a count or something to make sure I get a result.

Any help would be awesome

EDIT: Here is the full working function as PHP for anyone who finds this later

    function getUsersByRole( $roles ) {
    global $wpdb;
    if ( ! is_array( $roles ) ) {
        $roles = explode( ",", $roles );
        array_walk( $roles, 'trim' );
    }
    $sql = 'SELECT ID, display_name
        FROM '. $wpdb->users .'
        JOIN '. $wpdb->usermeta.' ON ('.$wpdb->users.'.ID = '.$wpdb->usermeta.'.user_id AND '.$wpdb->usermeta.'.meta_key = \''.$wpdb->prefix.'capabilities\')
        WHERE ID IN (SELECT post_author FROM ' .$wpdb->posts.')
        AND(';
    $i = 1;
    foreach ( $roles as $role ) {
        $sql .= ' ' . $wpdb->usermeta . '.meta_value    LIKE    \'%"' . $role . '"%\' ';
        if ( $i < count( $roles ) ) $sql .= ' OR ';
        $i++;
    }
    $sql .= ' ) ';
    $sql .= ' ORDER BY display_name ';
    $userIDs = $wpdb->get_col( $sql );

    return $userIDs;
}
share|improve this question
    
This seems OK, but it will return the same user multiple times because of the join. You now also restrict on editor or author: do you really need only the current authors or is it OK to return someone who used to be an author, wrote some posts back then, but currently has another role? Then you can leave out the join with usermeta and only join with the posts table. –  Jan Fabry Apr 7 '11 at 6:24
    
I'm trying to create an author list page that shows all authors and editors that have at least one post. I just DON'T want to return Admin and Subscribers. Subscribers won't have post anyway and Admin shouldn't have post either. –  BandonRandon Apr 7 '11 at 6:27
    
Shoud I run another query to get all the post from the user then only ruturn ID if has post? (if so a little help with that :) –  BandonRandon Apr 7 '11 at 6:29
    
How many users and/or posts might you have for this site? The solutions on this page will all perform rather poorly once you start getting into 10,000 and especially 100,000+ records. –  MikeSchinkel Apr 7 '11 at 9:27
    
@mike right now I have like 4 users. I'm open to other solutions. Really the query is only being ran if the vist the author page which shouldn't have that much traffic. As I said other solutions are welcome :) –  BandonRandon Apr 9 '11 at 1:52

1 Answer 1

up vote 2 down vote accepted

Instead of a JOIN, you can use a IN with a subquery, to prevent duplicate results because of the join:

SELECT ID, display_name
FROM wp_users
WHERE ID IN (SELECT post_author FROM wp_posts)

This will not look at capabilities. If you also want that you can add them like a join, but move the meta_key clause in the join, also to prevent duplicate results:

SELECT ID, display_name
FROM wp_users
    JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'wp_capabilities')
WHERE ID IN (SELECT post_author FROM wp_posts)
    AND (wp_usermeta.meta_value LIKE '%"author"%'
        OR wp_usermeta.meta_value LIKE '%"editor"%' )

Up to you to convert this back to PHP! Sidenote: Is the wp_ in wp_capabilities dependent on the database prefix, or is it always wp_, even if your table names start with another prefix?

share|improve this answer
    
I don't use the wp_ prefix and the meta key in the DB was set to my prefix. so the meta_key is ba_capabilities :) I think that's why if you look at the above code you'll see $wpdb->prefix . 'capabilities\' –  BandonRandon Apr 7 '11 at 6:44
    
@BandonRandon: Indeed, it was that line that made me think about it. Thanks for the confirmation! –  Jan Fabry Apr 7 '11 at 7:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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