The problem

There is a post_author post type, the main post relates to post_author: every post has one post_author.

I need to have a list of all post authors to be sorted in a descending order by author's post count, then order by author's name which is the post_title of the post_author post type.

Solution in SQL

SELECT a.ID, a.post_title FROM wp_posts a
WHERE a.post_type = 'author_cpt'
AND a.post_status = 'publish'
ORDER BY (
    SELECT count(DISTINCT p.ID) AS post_count FROM wp_posts p
    LEFT JOIN wp_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;

The question

Is there a way to have an equivalent version of the above query in WP_Query?

Current solution

I have applied a solution in two consequent steps:

1) Get all post authors

$queryArgs = [
    'post_type' => 'author_cpt',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'orderby'=> 'title',
    'order' => 'ASC',
];

$allAuthorsQuery = new WP_Query($queryArgs);

2) Loop through the query, construct a new array with post count, sort the resulted array.

$orderedAuthors = [];
if ( $allAuthorsQuery->have_posts() ) {
    while ( $allAuthorsQuery->have_posts() ) {
        $allAuthorsQuery->the_post();
        $postCount = getPostMetaCount('', get_the_ID());
        $orderedAuthors[] = [
            'ID' => get_the_ID(),
            'post_content' => get_the_excerpt(get_the_ID()),
            'post_count' => $postCount,
        ];
    }
}
wp_reset_query();
sortBy('post_count', $orderedAuthors, 'desc');

helper functions

/**
 * Get post count by meta_key and meta_value
 */
function getPostMetaCount($key = '', $value = '',  $type = 'post', $status = 'publish')
{
    if (empty($key))
        return;

    if (empty($value))
        return;

    $query = new WP_Query([
        'post_type' => $type,
        'post_status' => $status,
        'meta_query' => [
            [
                'key' => $key,
                'value' => $value,
            ]
        ],
    ]);

    return $query->found_posts;
}

/**
 * Sort an associative array by $field
 */
function sortBy($field, &$array, $direction = 'asc')
{
    usort($array, create_function('$a, $b', '
        $a = $a["' . $field . '"];
        $b = $b["' . $field . '"];

        if ($a == $b) return 0;

        $direction = strtolower(trim($direction));

        return ($a ' . ($direction == 'desc' ? '>' : '<') .' $b) ? -1 : 1;
    '));

    return true;
}

The problem with the current solution that I have lost the order by title after apply the sort in an array.

The overall objective

Transfer the order process to SQL, or equivalent WP_Query, in order to not being in need to sort an array like what introduced in the above solution.

Thanks!

The problem

There is a post_author post type, the main post relates to post_author: every post has one post_author.

I need to have a list of all post authors to be sorted in a descending order by author's post count, then order by author's name which is the post_title of the post_author post type.

Solution in SQL

SELECT a.ID, a.post_title FROM wp_posts a
WHERE a.post_type = 'author_cpt'
AND a.post_status = 'publish'
ORDER BY (
    SELECT count(DISTINCT p.ID) AS post_count FROM wp_posts p
    LEFT JOIN wp_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;

The question

Is there a way to have an equivalent version of the above query in WP_Query?

Current solution

I have applied a solution in two consequent steps:

1) Get all post authors

$queryArgs = [
    'post_type' => 'author_cpt',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'orderby'=> 'title',
    'order' => 'ASC',
];

$allAuthorsQuery = new WP_Query($queryArgs);

2) Loop through the query, construct a new array with post count, sort the resulted array.

$orderedAuthors = [];
if ( $allAuthorsQuery->have_posts() ) {
    while ( $allAuthorsQuery->have_posts() ) {
        $allAuthorsQuery->the_post();
        $postCount = getPostMetaCount('', get_the_ID());
        $orderedAuthors[] = [
            'ID' => get_the_ID(),
            'post_content' => get_the_excerpt(get_the_ID()),
            'post_count' => $postCount,
        ];
    }
}
wp_reset_query();
sortBy('post_count', $orderedAuthors, 'desc');

helper functions

/**
 * Get post count by meta_key and meta_value
 */
function getPostMetaCount($key = '', $value = '',  $type = 'post', $status = 'publish')
{
    if (empty($key))
        return;

    if (empty($value))
        return;

    $query = new WP_Query([
        'post_type' => $type,
        'post_status' => $status,
        'meta_query' => [
            [
                'key' => $key,
                'value' => $value,
            ]
        ],
    ]);

    return $query->found_posts;
}

/**
 * Sort an associative array by $field
 */
function sortBy($field, &$array, $direction = 'asc')
{
    usort($array, create_function('$a, $b', '
        $a = $a["' . $field . '"];
        $b = $b["' . $field . '"];

        if ($a == $b) return 0;

        $direction = strtolower(trim($direction));

        return ($a ' . ($direction == 'desc' ? '>' : '<') .' $b) ? -1 : 1;
    '));

    return true;
}

The problem with the current solution that I have lost the order by title after apply the sort in an array.

The overall objective

Transfer the order process to SQL, or equivalent WP_Query, in order to not being in need to sort an array like what introduced in the above solution.

Thanks!

• wp-query
• sql

• Is there any reason why authors can not be managed with user profiles and roles? – cybmeta Commented Apr 10, 2020 at 7:05
• Actually it's been driven by a business rule there was a need to transfer the logic this way. in addition to limit the authority of the business owners. Do you have a suggestion that solves the problem using user profiles and roles. Thank you! – mohjak Commented Apr 11, 2020 at 13:50
• @mohjak Sorry that my previous answer was not addressing exact problem. I have modified the answer and hopefully it could illustrate the idea. You could either use WP_Query with meta_key by adding post_count field OR you could use your original SQL directly to create the query. Wish that I have understood your question correctly. – 西門 正 Code Guy - JingCodeGuy Commented May 3, 2020 at 14:29

1 Answer 1

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);