List users by sum of total custom fields for all their Posts

  • Sarah_Frantz

    (@sarah_frantz)


    11 years ago

    Ok, so I have a running site, and users can log their milage. Miles are a custom post type, with a custom field value as the actual number for the miles.

    I want to list the users by who has the most miles, I know how to get all the miles for each user individually on their page, but I can’t figure out the foreach loop that I think I need… or if I need a custom database query.

    Any help is greatly appreciated. Here is how I’m getting all of the sum of the custom field values for all their posts combined, I just need to do this for each user, and output them in a list format, with how many miles each has run total.

    <?php
			//get current user
			global $current_user;
		    get_currentuserinfo();
			// build query of ids by user
			$userPosts = get_posts(array('author' => $current_user->ID, 'post_type'=> 'miles')); //change this
			// loop to create array of ids by user
			foreach ($userPosts as $post) {
			    setup_postdata($post);
			    $ids[] = get_the_ID();
			}
			$idList = implode(",", $ids); //tun this crap into a list

			$meta_key = 'miles';//set this to your custom field meta key
			$allmiles = $wpdb->get_col($wpdb->prepare("
			                                  SELECT meta_value
			                                  FROM $wpdb->postmeta
			                                  WHERE meta_key = %s
			                                  AND post_id in (" . $idList . ")", $meta_key));
			echo '<p>You\'ve completed ' . array_sum( $allmiles) . '</p>';	?>
Viewing 1 replies (of 1 total)

  • Here is some code that should get you started:

    $sql = "SELECT u.ID, um1.meta_value AS first_name, um2.meta_value AS last_name,
            SUM(pm.meta_value) AS total_miles
         FROM $wpdb->users u
         JOIN $wpdb->usermeta um1 ON um1.user_id = u.ID
         JOIN $wpdb->usermeta um2 ON um2.user_id = u.ID
         JOIN $wpdb->posts p ON p.post_author = u.ID
         JOIN $wpdb->postmeta pm ON pm.post_id = p.ID
         WHERE um1.meta_key = 'first_name'
         AND um2.meta_key = 'last_name'
         AND p.post_type = 'post' AND p.post_status = 'publish'
         AND pm.meta_key = 'Miles'
         GROUP BY u.ID
         ORDER BY total_miles DESC
         ";
   $results = $wpdb->get_results($sql);
   //print_r($results);
   foreach ($results as $row) {
      echo "$row->first_name $row->last_name  $row->total_miles<br />";
   }
Viewing 1 replies (of 1 total)
  • The topic ‘List users by sum of total custom fields for all their Posts’ is closed to new replies.