1

I am trying to make a query to take first 3 categories with most posts per user, but no luck so far. This is where I am now:


SELECT DISTINCT(terms.term_id) as term_ID, terms.name, terms.slug, posts.post_author, t0.count_id

FROM wp_posts as posts
JOIN wp_term_relationships as relationships ON posts.ID = relationships.object_ID
JOIN wp_term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
JOIN wp_terms as terms ON tax.term_id = terms.term_id

JOIN (
SELECT COUNT(*) as count_id, count_terms.term_id
FROM wp_posts as posts_count
JOIN wp_term_relationships as count_relationships ON posts_count.ID = count_relationships.object_ID
JOIN wp_term_taxonomy as count_tax ON count_relationships.term_taxonomy_id = count_tax.term_taxonomy_id
JOIN wp_terms as count_terms ON count_tax.term_id = count_terms.term_id
WHERE count_tax.taxonomy = "category"
AND posts_count.post_status = "publish"
AND posts_count.post_author in (1,2,3)
group by count_terms.term_id

) as t0 on posts.post_author in (1,2,3)

WHERE tax.taxonomy = "category"
AND posts.post_status = "publish"
AND posts.post_author in (1,2,3)

Which returns the users with categories, but with total sum of all the posts in those categories for this users. And I need first 3 categories with most posts per user. Any idea how to do that?

0

Your question is slightly ambiguous, but I read it this way: given an author and you want to know the three categories in which has published most posts. Let's use wp_query to get there.

$author = 'johndoe';
$author_posts = new WP_Query (array (
  'author' => $author,
  'posts_per_page'=>-1,
  ));

Now we have an array of all posts by this author. Sadly it's not possible to order them by category (because a post may have multiple categories), let alone by amount of posts per category. So we'll have to loop through the results and do the counting ourselves.

 $cat_array = array();
 if ( $author_posts->have_posts() ) {
   while ( $author_posts->have_posts() ) {
     $author_posts->the_post();
     $cat_array[] = wp_get_post_categories(get_the_ID(),array('fields'=>'names'));
     }
   }

This gives an array $cat_array which simply holds all the categorie names of all the author's posts. We will have to count the duplicates to see which category is used most. There's a PHP function for that: array_count_values.

 $cat_count = array_count_values($cat_array);
 $cat_count = arsort($catcount); // sorts an array by value from high to low

We end up with $cat_count holding the list of categories by this author from high to low occurence. Depending on what you want you could use this information to loop through $author_posts again or do a new query to get all the posts in the three top categories:

$author = 'johndoe';
$top_cats = $cat_count[0] . ',' . $cat_count[1] . ',' . $cat_count[2];
$author_posts = new WP_Query (array (
  'author'         => $author,
  'posts_per_page' =>-1,
  'category_name'  => $top_cats,
  ));

Beware that this is a fairly expensive procedure in terms of computation. Also note that I didn't test this code, so some debugging may be necessary.

2
  • Yes, indeed is expensive procedure, that's why I want it to get results from the database with one query. With your example I should make a couple requests. I have to use this for listing of users and I don't think I can use your code, but thank you for time and response. – user1952854 Sep 24 '16 at 11:43
  • I don't think it can be done in one query, because you need some counting/sorting halfway narrowing down your search. – cjbj Sep 24 '16 at 11:47
0

Write this function in your functions.php or plugin file-

function wpse_240422_get_author_post_categories( $author_id ) {

    /**
     * this $categories array will contain all the post categories an author posted in
     */
    $categories = array();

    /**
     * gather all the posts publised by $author_id
     */
    $posts = query_posts( array( 'author' => $author_id, 'posts_per_page' => -1 ) );

    /**
     * loop through posts, take their categoreis and store them in $categories array
     */
    foreach ( $posts as $post ) {
        foreach ( get_the_category( $post->ID ) as $category ) {
            $categories[] = $category->term_id;
        }
    }

    /**
     * now count the number of times every category was used
     */
    $category_counter = array_count_values( $categories );

    /**
     * sort them from high to low
     */
    arsort( $category_counter );

    return $category_counter;
}

Use: To get post categories used by author 1, just pass the author id to this function and take only the keys. For example:

$author_categories = array_keys( wpse_240422_get_author_post_categories( 1 ) );

Now, this $author_categories will contain all the categories this author posted in.

Finally, use array indexes to get your desired category-

$most_1st = $author_categories[0]; // top most 'used' category
$most_2nd = $author_categories[1]; // second most 'used' category
$most_3rd = $author_categories[2]; // third most 'used' category

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.