List users by sum of all their posts’ custom field values

0 Views

So I can do this by user, individually, but I can’t figure out how to put it in a foreach loop.

What I want to do is output something like this:

Read More

Author, Miles

Where miles is the sum of every single post that contains the custom field miles, associated with that author. And then list them from greatest to least.

My code to do this when the user is viewing their own mileage looks like this: 

    <?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>';  ?>

I’m not sure how to replicate this, so it happens for each author, in a list format…
thoughts??

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. You would probably be best of writing your own SQL query, for performance-reasons. Another option is to store the mileage separately in a custom field for each user. As for writing your own SQL query: you could query all users, then join their posts, then join the postmeta-table.

    After that, you can use the SQL SUM-function to take the sum of miles-fields in the postmeta-table, per user.

    This would result in a query like this (using $wpdb):

    global $wpdb;

$results = $wpdb->get_results( "
    SELECT
        us.ID,
        us.user_login,
        SUM( pm.meta_value ) AS mileage
    FROM
        {$wpdb->users} us
    JOIN
        {$wpdb->posts} po
        ON
        us.ID = po.post_author
    JOIN
        {$wpdb->postmeta} pm
        ON
        po.ID = pm.post_id
    WHERE
        pm.meta_key = 'miles'
        AND
        po.post_status = 'publish'
    GROUP BY
        us.ID
" );

    We start be selecting from the users-table. After this, we JOIN all posts. By using GROUP BY and joining the posts and postmeta after that, we retrieve all miles-postmeta-entries per user. This allows us to use SUM( pm.meta_value ) in selecting the columns, so we can get the total mileage per user.