Get user's most published categories

Question

I am working on listing all users similar to the Users page here on SE.

When listing each user, I want to add their 2 categories they have published most into. All categories are within Posts.

Performance is essential, therefore I've come up with a sufficient solution:

1. Make a function (with $user_id as argument) that returns category name of the user's two most published categories.
2. Create a daily wp_schedule_event() WP Cron that uses the above function and gets all users respective categories and finally adds it to each user's metadata via add_user_meta().
3. Simply get the users' categories via get_user_meta() on the Users page.

Currently, I have the cron and metadata codes ready, all I am missing is the function that returns the users top 2 categories. Can you help with this function?

Answer 1

This can be done easily with $wpdb, here's my approach:

function GetTop2CategoryByUser($user_id, $taxonomy){
    global $wpdb;

    $results=$wpdb->get_results( $wpdb->prepare( 
     "
       SELECT      tt.term_id as category, COUNT(p.ID) as count
       FROM        $wpdb->posts p

       JOIN        $wpdb->term_relationships tr 
                   ON p.ID = tr.object_id

       JOIN        $wpdb->term_taxonomy tt
                   ON tt.term_taxonomy_id = tr.term_taxonomy_id
                   AND (tt.taxonomy = %s AND tt.term_taxonomy_id != 1)

       WHERE       p.post_author = %s
       GROUP BY    tt.term_id
       ORDER BY    count DESC LIMIT 2
    ",
    $taxonomy,
    $user_id
   ) );
 return $results;
}

// Get user's top 2 published categories
$user_top_cat = GetTop2CategoryByUser($user_ID,$taxonomy)

// $results should return
Array
(
    [0] => stdClass Object
        (
            [term_id] => 4
            [count] => 8345
        )
    [1] => stdClass Object
        (
            [term_id] => 3
            [count] => 45345
        )
)

It's slightly faster retrieving the category ID's instead of names.

Answer 2

<?php 

  $key = 'user_top_categories'; // User meta key for the top categories
  $single = false; // Array or String
  $top_categories = get_user_meta( $user_id, $key, $single );

  foreach($top_categories as $top_cat){

     $term = get_term_by('name', $top_cat, 'category');
     $term_link = get_term_link( $term );

     echo '<li><a href="' . esc_url( $term_link ) . '">' . $term->name . 
          ' ('. $term->count .')</a></li>';

  }

?>