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
    Commented Aug 31, 2014 at 23:27
  • @Ervald Category names. Array or string, it does not matter.
    – Christine Cooper
    Commented Sep 1, 2014 at 13:10
  • Gave you an answer with meta categories as array.
    – Stubbies
    Commented Sep 1, 2014 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
    Commented Sep 1, 2014 at 22:32

2 Answers 2

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
    Commented Sep 2, 2014 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
    Commented Sep 2, 2014 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
    Commented Sep 3, 2014 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
    Commented Sep 3, 2014 at 21:24
  • 1
    Changed the code it should skip category 1 now. As for optimization, how many categories do you have?
    – Stubbies
    Commented Sep 4, 2014 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
    Commented Sep 1, 2014 at 22:27
  • Check my new answer.
    – Stubbies
    Commented Sep 1, 2014 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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