0

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!

3
  • Is there any reason why authors can not be managed with user profiles and roles? – cybmeta Apr 10 '20 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 Apr 11 '20 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. – simongcc May 3 '20 at 14:29
0

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);
3
  • Thank you @simongcc. I will test your solutions and update you. – mohjak May 5 '20 at 10:41
  • You are welcome. About the custom field post_count, because I am unsure of where should you save since you have mentioned that it is a custom post type. So you may adjust it. The concept is if you order by post_count in custom_post_type, that custom post type is assumed to have such custom field and field value for query. The example used user_meta as example and illustration. You may need to change to normal post meta function later to match the code. – simongcc May 5 '20 at 12:21
  • BTW, you may use custom user meta field to solve the problem I think. But if you concern the security/access right, maybe adding capabilities or user roles to control. It depends on how eager you won't like to separate it or transfer to logic to user profile. – simongcc May 5 '20 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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