WP Query group/order by category name

1 Views

I have a requirement for displaying a list of custom post types grouped by category, but in addition to this, each custom post is linked with a meta value (in this example lets say we have a custom meta value of “TEST1”).

Basically, my goal is to display a list, grouped by category for all custom posts with a code of “TEST1”. The way I am currently doing this is by simply getting all categories linked to my custom post type (ignoring empty), then looping through each category to get all posts within it that have a code of “TEST1”.

Read More

Now this is great, it works fine, however it is extremely inefficient. At the moment this isn’t an issue but I fear that it won’t scale very well as the number of categories increases overtime – because each iteration equals another DB hit (and join to the post meta table).

I have had a good look across the web but I am unable to find anything but was wondering if it is possible to perform a WP_Query call that would return all posts with the custom meta value of “TEST1” grouped or ordered by their category name? This would be a lot more efficient (basically 1 DB hit I would assume). If anyone can advise anything here I would be really grateful, if it is not possible then I assume my only way to achieve this would be to do a direct query to the database from within my plugin (which from what I’ve read is not advised when working with plugins).

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. There are a few ways you can do this.

    The simplest is to just get everything: posts and terms, then use some array_filter magic to group things. Simple example (that will only work with PHP 5.3+):

    <?php
$terms = get_terms('your_taxonomy');

$term_ids = array_map(function($t) {
    return $t->term_id,
}, $terms);

$posts = get_posts(array(
    'nopaging'      => true,
    'tax_query'     => array(
        array(
            'taxonomy'  => 'category',
            'field'     => 'id',
            'terms'     => $term_ids,
        ),
    ),
    'meta_query'    => array(
        array(
            'key'       => 'TEST1',
            'compare'   => 'EXISTS', // wp 3.5+ only
        )
    ),
));

foreach ($terms as $t) {
    $posts_in_term = array_filter($posts, function($p) use ($t) {
        // has_term likely triggers a DB hit...
        return has_term($t->term_id, 'your_taxonomy', $p);
    });

    // do stuff with $posts_in_term 
}

    This is fairly easy to understand. You take a lot of the ordering work you would have sent to the DB and did it in the PHP app itself. Not bad, certainly fewer DB hits than you were doing. If you need to put a headline with each category name, this would likely be the way to go because it’s going to be very difficult with the next method.

    Option 2: Do the get_posts query as normal, but hook into posts_groupby and order by term ID. You need need to do some digging to figure out the table aliases/names to order by in the WP_Tax_Query class which creates table aliases based on how many taxonomy queries are in a given set. Since we only have one, there is no allias and you just need to add $wpdb->term_relationships.object_id to the group by. The end result is something that looks like GROUP BY $wpdb->posts.ID, $wpdb->term_relationships.object_id.

    Example:

    <?php
// our group by callback
function wpse84243_groupby($groupby, $query) {
    global $wpdb;

    return $groupby . ' ' . $wpdb->term_relationships . '.object_id';
}

add_filter('posts_groupby', 'wpse84243_groupby', 10, 2);
$posts = get_posts(array(
    'nopaging'      => true,
    'tax_query'     => array(
        array(
            'taxonomy'  => 'category',
            'field'     => 'id',
            'terms'     => get_terms('category', array('fields' => 'ids')),
        ),
    ),
    'meta_query'    => array(
        array(
            'key'       => 'TEST1',
            'compare'   => 'EXISTS', // wp 3.5+ only
        )
    ),
));

// remove the filter, put things back to normal
remove_filter('posts_groupby', 'wpse84243_groupby', 10, 2);

// do stuff with $posts, they'll be grouped by your taxonomy ID.