2

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?

4
  • Are you saving category id's or names? And as array or string? – Stubbies Aug 31 '14 at 23:27
  • @Ervald Category names. Array or string, it does not matter. – Christine Cooper Sep 1 '14 at 13:10
  • Gave you an answer with meta categories as array. – Stubbies Sep 1 '14 at 17:49
  • @Ervald My apologies, with "saving category id's or names", I misunderstood your question, I do not currently have category names/id's saved in the user's metadata. My aim is to make a function to get the user's top 2 most published categories. I think if you reread my question, I believe it is clear enough. – Christine Cooper Sep 1 '14 at 22:32
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.

9
  • I am not sure if this is working correctly. I ran this with a random $user_id and this is is the returned array: Array ( [0] => stdClass Object ( [term_id] => 2412 [count] => 808 )[1] => stdClass Object ( [term_id] => 28 [count] => 625 ))... If term_id is category ID, then we definitely do not have category ID's equaling 2412 or 28. Or am I missing something? Please advice. Btw, I will edit your answer to wrap it into a function with $user_id as argument. – Christine Cooper Sep 2 '14 at 14:03
  • My mistake, wp uses taxonomy relationship for the menu as well, I've edited the query and added this line AND tt.taxonomy = 'category' where 'category' is the name of the taxonomy, if you have a custom taxonomy change it. – Stubbies Sep 2 '14 at 17:20
  • Thank you for the update, although it appears there are still problems. When I run $user_top_cat = GetTop2CategoryByUser($user_ID,'category'); -- I get an array with two term_ids (category IDs). The problem is that these two aren't the users most published categories. It is correct that the user has published in those categories, but it is definitely not the user's top 2 most published categories. I tried it with several users and the same result unfortunately. Can you please confirm this? Is there an issue with the query? – Christine Cooper Sep 3 '14 at 13:48
  • 1
    There were few problems with the query (mainly the count) which gave wrong results. Edited the code, hopefully you get the desired results. On my local testing it works. – Stubbies Sep 3 '14 at 21:24
  • 1
    Changed the code it should skip category 1 now. As for optimization, how many categories do you have? – Stubbies Sep 4 '14 at 20:29
0
<?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>';

  }

?>
2
  • I believe you may have misunderstood my question. I want a function to get the user's top 2 most published categories. I think you are doing something else in your answer. – Christine Cooper Sep 1 '14 at 22:27
  • Check my new answer. – Stubbies Sep 1 '14 at 23:52

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.