Because I don’t have the exact settings, I just make use of the variables showing in the questions to code. The code is tested with similar settings with meta value.
If using WP_Query object, one more custom field is required to achieve the effect
$queryArgs = [
'post_type' => 'author_cpt',
'post_status' => 'publish',
'posts_per_page' => -1,
'orderby'=> 'title',
'order' => 'ASC',
'meta_key' => 'post_count',
'orderby' => 'meta_value_num', // sort as number
'meta_query' => array(
array(
'key' => 'post_count',
),
),
];
$allAuthorsQuery = new WP_Query($queryArgs);
// to make the following work, need to add save post count for author to author's meta value
// and then fetch the author's meta value when fetch from WP_QUERY
// structure is sames as $allAuthorsQuerybySQL, but all default fields are fetched
// pros: all fields are fetched
// cons: one additional meta field is required
// var_dump($allAuthorsQuery->get_posts());
Init for empty newly added field and update post count when save post
The following illustrate the post_count parameter saved to user’s meta.
According to the question, because it is a custom post_type, so it is required to adjust where to save the post count. For using meta value with WP_Query
with author_cpt
. The post_count is needed to be saved in the same post_type as that post type meta in order to fetch.
The following illustrate the idea.
// init for first time run, run once is enough after adding post_count field
$authorArgs = [
'post_type' => 'author_cpt',
'post_status' => 'publish',
'posts_per_page' => -1,
'orderby'=> 'title',
'order' => 'ASC',
];
$allAuthorsQuery = new WP_Query($authorArgs);
$authorIDs = array();
if ( $allAuthorsQuery->have_posts() ) {
while ( $allAuthorsQuery->have_posts() ) {
$allAuthorsQuery->the_post();
$authorIDs[] = get_post_meta( get_the_ID(), 'author_id', true );
}
wp_reset_postdata();
}
// to avoid query inside query, so handle separately, add to each author's post_count field
foreach ( $authorIDs as $key => $authorID ) {
$postQuery = new WP_Query([
'post_type' => 'post',
'post_status' => 'publish',
'author' => $authorID,
]);
update_user_meta( $authorID, 'post_count', $postQuery->found_posts );
}
// save to author's new custom field
update_user_meta( $post->post_author, 'post_count', $query->found_posts );
// update post count when save a new post
add_action( 'save_post', 'ws363633_save_post_count', 10, 3 );
function ws363633_save_post_count( $post_ID, $post, $update ) {
if ( defined('DOING_AUTOSAVE') && DOING_AUTOSAVE )
return $post_ID;
// Only save post's post count
if ( isset( $_POST['post_type'] ) && ( $post_type_object = get_post_type_object( $_POST['post_type'] ) ) && $post_type_object->public && $_POST['post_type'] === 'post' ) {
if ( current_user_can( 'edit_post', $post_ID ) ) {
$query = new WP_Query([
'post_type' => 'post',
'post_status' => 'publish',
'author' => $post->post_author,
]);
// save to author's new custom field
update_user_meta( $post->post_author, 'post_count', $query->found_posts );
// for intercept debug
// var_dump(get_user_meta( $post->post_author, 'post_count', $query->found_posts ));
// var_dump($query->found_posts);
// exit();
}
}
return $post_ID;
}
If using the original solution, could use the following query method
// cons:
// only fetch the fields you have added to the SQL statement
// pros:
// flexible
$allAuthorsQuerybySQL = $wpdb->get_results("SELECT a.ID, a.post_title FROM {$wpdb->prefix}posts a
WHERE a.post_type="restaurant"
AND a.post_status="publish"
ORDER BY (
SELECT count(DISTINCT p.ID) AS post_count FROM wp_posts p
LEFT JOIN {$wpdb->prefix}postmeta pm ON (p.ID = pm.post_id)
WHERE pm.meta_key = 'author_id'
AND pm.meta_value = a.ID
AND p.post_type="post"
AND p.post_status="publish"
) DESC, a.post_title ASC", OBJECT );
// var_dump($allAuthorsQuerybySQL);