1

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:

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

1
  • miles is a custom post type or meta field? because in your get_posts(), you've specified post_type as miles?
    – Kumar
    Mar 29 '14 at 7:38
0

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.

3
  • Good idea... this actually worked great, and was just what I needed.
    – Sarah
    Mar 29 '14 at 23:43
  • Dumb question. I've never worked with custom sql queries and wordpress, but how would I format the results? in a list, for example.
    – Sarah
    Mar 29 '14 at 23:47
  • I suggest you actually try the code used in the answer and print the results using print_r( $results ). The get_results()-method of $wpdb returns an array of objects with, in this case, the properties ID, user_login and mileage. Loop through this array and do with it as you see fit :-).
    – engelen
    Mar 30 '14 at 6:41

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.