Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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".

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).

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.
share|improve this answer
    
This is great, thanks a lot for your help, going to opt for the array_filter method, wasn't aware of being able to do this. –  hdougie Feb 9 '13 at 20:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.