Return ID of authors who have at least one post

1 Views

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

Read More

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;
}
Post Views: 1

Related posts

Leave a Reply

1 comment

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