Order custom posts by taxonomy, then by meta_key

Question

I've created a custom post type called Player Manager, where I have all the players of a football team. The CPT has four different categories - goalkeepers, defenders, midfielders and strikers. I also gave it a few text fields, where the only important one, for now, is "number".

Here's how I'd like it to show up in the archives page:

Goalkeepers
-1
-21
-24
Defenders
-3
-4
-5
-11

So, what I want is for the posts to be ordered first by the categories and then, within the categories, by the numbers. I've managed to have them ordered by either categories or numbers (right now by numbers):

$querystr = "
SELECT $wpdb->posts.*
FROM $wpdb->posts, $wpdb->postmeta
WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
AND $wpdb->posts.post_type = 'players'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->postmeta.meta_key = 'number'
ORDER BY ABS ($wpdb->postmeta.meta_value) ASC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);

But that's not how I'd like it. I guess I could do the same loop 4 times, but with different categories.

Answer 1

So, first off, I would use get_posts instead of writing your own query.

The strategy: fetch all the posts at once, then filter them using a callback or foreach loops. What follows is a PHP 5.3+ example (anonymous functions, etc).

Let's wrap all this up in a function that will take a post type, the terms you want, and the taxonomy to which they belong.

<?php
function wpse63444_get_posts($post_type, $terms, $tax)
{

}

So then we can get the posts.

<?php
function wpse63444_get_posts($post_type, $terms, $tax)
{
    $posts = get_posts(array(
        'post_type'     => $post_type,
        'meta_key'      => 'number', // the meta key
        'order_by'      => 'meta_value_num',
        'order'         => 'ASC', // might have to tweak the order a bit
        'numberposts'   => -1, // get ALL THE POSTS
        'tax_query'     => array(
            array(
                'taxonomy'          => $tax,
                'field'             => 'slug',
                'terms'             => $terms,
                'include_children'  => false,
            ),
        ),
    ));

    if(!$posts)
        return array(); // bail if we didn't get any posts
}

Now that we have all the posts, and we know the terms, we can filter them into array of term => posts pairs.

<?php
function wpse63444_get_posts($post_type, $terms, $tax)
{
    // snip snip

    $res = array();

    foreach($terms as $t)
    {
        // PHP < 5.3 will need something different here
        $res[$t] = array_filter($posts, function($p) use ($t, $tax) {
            if(has_term($t, $tax, $p))
                return $p; // the post has this term, use it
        });
    }

    return $res;
}

The entire function:

<?php
function wpse63444_get_posts($post_type, $terms, $tax)
{
    $posts = get_posts(array(
        'post_type'     => $post_type,
        'meta_key'      => 'number', // the meta key
        'order_by'      => 'meta_value_num',
        'order'         => 'ASC', // might have to tweak the order a bit
        'numberposts'   => -1, // get ALL THE POSTS
        'tax_query'     => array(
            array(
                'taxonomy'          => $tax,
                'field'             => 'slug',
                'terms'             => $terms,
                'include_children'  => false,
            ),
        ),
    ));

    if(!$posts)
        return array(); // bail if we didn't get any posts

    $res = array();

    foreach($terms as $t)
    {
        // PHP < 5.3 will need something different here
        $res[$t] = array_filter($posts, function($p) use ($t, $tax) {
            if(has_term($t, $tax, $p))
                return $p; // the post has this term, use it
        });
    }

    return $res;
}

Some example usage with normal posts and categories found in the theme unit test data.

<?php
$res = wpse63444_get_posts('post', array('cat-a', 'cat-b', 'cat-c'), 'category');

if($res)
{
    foreach($res as $cat => $posts)
    {
        if(!$posts)
            continue;

        echo '<h1>', get_term_by('slug', $cat, 'category')->name, '</h1>';
        foreach($posts as $p)
            echo '<h2>', $p->post_title, ' ', get_post_meta($p->ID, 'number', true), '</h2>';
    }
}

Here is that function wrapped up in a plugin