List Authors by Total Post Count and display with Content Template Block Element

Found a way to get wordpress authors in order by the number of custom posts published.

Here is the code:

$authors = $wpdb->get_results(
    "SELECT
        $wpdb->users.ID AS author_id,
        $wpdb->users.display_name AS author_name,
        COUNT($wpdb->posts.ID) AS published_songs_and_poems,
        COUNT(CASE WHEN $wpdb->posts.post_type = 'song' THEN $wpdb->posts.ID ELSE NULL END) as published_songs,
        COUNT(CASE WHEN $wpdb->posts.post_type = 'poem' THEN $wpdb->posts.ID ELSE NULL END) as published_poems
    FROM $wpdb->users
    JOIN $wpdb->posts
        ON $wpdb->posts.post_author = $wpdb->users.ID
        AND $wpdb->posts.post_type IN('song', 'poem') AND $wpdb->posts.post_status = 'publish'
    GROUP BY author_id
    ORDER BY published_songs_and_poems DESC"
);

array_walk($authors, function($author) {
    echo $author->author_name." has published ".$author->published_songs." song(s) and ".$author->published_poems." poem(s).<br/>";
});

We'll adjust this code to include regular post type as well.

Is there a way to use Content Block Element that we already have designed to display the results?
It doesn't have to be on the actual archive page of CPT. We can create another page to list the authors, if it's too complicated to do this on the archive page.

Hi there,

the Block element content template will work on any archive template that uses the generate_do_template_part in the loop - see here for an example in our archive.php:

https://github.com/tomusborne/generatepress/blob/8bff7e067bb4df92ce1711fddb843585d4d18e71/archive.php#L47

Then you could use the pre_get_posts filter to pass in your orderby and order parameters into the WP_Query.
Heres an example using a custom post meta value:

https://generatepress.com/forums/topic/cpt-archive-page-all-posts-order-by-cpts-category-taxonomy/page/2/#post-2165880

Thank you, but I couldn't get it to work.

So far I can sort by order of published date by setting order to DESC or ASC.
Setting orderby = post_count doesn't do anything.

I'm bad with php and I couldn't figure out how to write the snippet to do the following:
Author profiles live in team CPT. Each profile is created by a WordPress user.
These users are also authors or regular blog posts post_type('post')
The goal is to display a list of author profiles (team archive) and sort them in order of number of regular blog posts they have contributed.

This is the snippet I've been playing with:

function custom_cpt_order($query){
    if( !is_admin() && ( is_post_type_archive('team') ) && empty( $query->query_vars['suppress_filters'] )){
        $query->query_vars['meta_key'] = '';
	$query->query_vars['orderby'] = 'post_count';
        $query->query_vars['order'] = 'DESC';
    }
    return $query;
}

add_filter( 'pre_get_posts', 'custom_cpt_order' );

Thanks a lot for your help.

So this: $query->query_vars['orderby'] = 'post_count'; will only work if you were querying the Users.

e.g $users = get_users('orderby=post_count');

So the question here is how do you get the post_count for each of the authors and store that in some custom post meta in your CPT. Not sure on this one.

Hey guys, been on this problem for weeks now.
Any ideas?

Cheers!

So the question here is how do you get the post_count for each of the authors and store that in some custom post meta in your CPT.

David asked a question here, can you answer it so we can have a better understanding of the situation?