48

My question is simple, I'm using WP_Query to retrieve some custom type posts filtering by a taxonomy using tax_query.

Now my problem is I would like to orderby the taxonomy, but from documentation and searching in the web I can't find a solution.

The orderby in WP_Query let's you order by a bunch of fields even custom meta fields but it doesn't seem to support taxonomy.

Any pointers on the right direction?

Thank you all.

13

No, it is not possible to order by taxonomy, because from a certain type of standpoint, that doesn't actually make much sense.

Taxonomies are ways to group things together. So the point of having a taxonomy on posts would really be to have terms in that taxonomy that are shared between posts. If a taxonomy had terms that were only used on one post each, then that would make the taxonomy kind of pointless. And if the terms were shared like they should be, then ordering by it wouldn't produce anything particularly useful.

What you should be using in such a situation is the post meta. You can order by post meta, and it's unique to each post.

Edit: That said, you can order by taxonomy by making a custom SQL query using a filter, you just can't do it from a unmodified WP_Query: http://scribu.net/wordpress/sortable-taxonomy-columns.html

However, if you're having to resort to doing this sort of thing, then your data design structure is wrong in the first place. "Terms" in the taxonomy are not actual "data". The terms themselves have no inherent meaning, they're just labels for the particular grouping that they're describing. If you're treating them as meaningful data, then you have an underlying design flaw.

Taxonomies group things by assigning terms to them. That grouping is the whole point of taxonomies, the terms are just pretty faces on the grouping. If you have meaningful metadata to assign to a post, then you should be using the post meta for it instead. And that you can order by, because post meta uses both keys and values to store information. With a taxonomy, you're really only storing keys, with their values being the posts grouped together by that term.

Things are easier in the long run if you use the correct approach for it. While I'm not saying that you can't do something strange with taxonomy, you're just making things harder for yourself in the long run by using it wrong.

  • Hello Otto, thank you for the answer. I see your point and maybe I am going the wrong way with this. In my example a site of tv shows, I have taxonomy for series 1, serie 2, serie 3 etc.. So I can group all the different tv shows by series number. Then I have the same for episodes, Episode 01, Episode 02, etc.. What I would like is when showing a list of all the episodes to be order by episode and serie. I will analyse then post meta and custom fields. Thank you Otto. – yeope Apr 8 '11 at 16:35
  • @yeope your taxonomy should be series and your terms should be series 1, series 2 etc. With episodes I'm assuming a series contains multiple episodes so it could use the same taxonomy, "series" and if they are hierarchal then episode 1, episode 2 etc would have the parent term "series x". Then you could query a whole series in order with the episodes falling in line where they should. – Chris_O Apr 8 '11 at 19:17
  • @Chris_O I see, you might be on the money there! The only problem I can see is the fact of having to repeat the terms "Episode 1", "Episode 2" for each series. Also not being able to group all episodes 1 not depending on the series, but I think there is probably a way around it. Thank you Chris_O – yeope Apr 8 '11 at 22:26
  • 2
    Using a taxonomy for episodes doesn't make much sense, actually, because the grouping is worthless. Think about it, if you have "episode 1" as a term, then you're grouping episode 1 with every other episode 1 from every other TV show. The episode and series numbers make more sense as post_meta, because they're specific to that particular show, and not useful as a group. The name of the TV show would be useful as a term in a tv-show taxonomy, because then you're grouping the show as a whole together. – Otto Apr 15 '11 at 19:28
  • 1
    Otto followed this up with an interesting blog post: When to (not) use a Custom Taxonomy. – Jan Fabry May 5 '11 at 6:55
44

The accepted answer for this question is unacceptable. It is illogical to assume that ordering by tax "doesn't make sense". The answer he gave doesn't make sense.

Consider having a menu post type. Then you have a custom tax of "FoodCategories". The FoodCategories tax has "Breakfast", "Lunch" and "Dinner" terms. If you submit a query utilizing the tax_query param, you now have a result set with all of the terms, however they are ordered by post date.

In order to get the correct order out of these, relative to their terms, and then to display on the front end appropriately by separating the posts into their various categories, you have to loop through the result set, then query each individual post within the result set to find it's terms and compare to the current term, filter into an array and continue throughout. Then you have to again loop through the new array for display. This is not productive.

It would be nice if WP had a "tax__in" orderby option as it does a "post__in" one, but since it does not, you either have to do the above ridiculous process; customize the query yourself by way of 'posts_orderby' filter and 'posts_join' filter in order to adjust the orderby method and add the term to the result set, respectively; or you have to make a new query for each term you are filtering for within the html sections relative to those terms.

The most efficient would be to change the query string by way of filters. The easiest would be to do three separate queries. The WP API should be handling ordering by tax, or any restrictive query parameters. If you are restricting a query based on certain conditions, there is a high probability that many will have need to order by those same conditions.

  • 2
    Sorry, but you are wrong. Ordering by taxonomy doesn't make any sense in your case either. What do you want to show? All the Breakfasts first, followed by all the Dinners, then all the Lunches? You should select what you want and the order in which you want it, but the taxonomy is just a grouping label. It's not meaningful "data" that you should be ordering by. If it is, then it shouldn't be a term in a taxonomy, you should make it a post-meta instead. – Otto Oct 20 '14 at 11:57
  • 15
    C'mon, of course there are going to be some instances in which you will want to order posts by taxonomy term. Another example is a Movie post type with a Rating taxonomy. In a list of movies, it's very easy to imagine people wanting to order a list of movies by rating so all G-rated, then PG-rated, etc. movies appear at the top. (In this and the meal example they could be ordered by term_id instead of name.) There's a large gray area of instances where you're probably best served by a taxonomy and not meta, but it's probably also helpful for that taxonomy to be order-able. – SeventhSteel Mar 10 '15 at 20:51
  • 2
    PG and G ratings and such are a good taxonomy choice, except that they are data about specific movies. Thus, they're meta. They're data, not categories. Just having a limited number of choices does not a taxonomy make. If it needs sort by, then either make it meta, or force the sort by the taxonomy via taxonomy specific code. BTW, NC17 comes after PG. So, you need code to do that ordering anyway. – Otto Nov 10 '15 at 1:05
  • I know I'm late to the party with this comment, but just bumped into this. Ordering by taxonomy can make sense in some situations. We have job listings on one project as a post type and then State and City in which the job is are taxonomies. We want them to be easily grupable (show all jobs in a state or show all jobs in a city) so taxonomy was the best solution. At the same time there is general job search where we want to sort them first by title, then by state, then by city. – Dennis Puzak Oct 23 '18 at 10:39
  • Another use case: A client has a bunch of articles, each of which has a category. The client wants there to be a page listing all the articles, which can be sorted alphabetically, by date, or by category. Categories can also be filtered, but listing all the articles by category alphabetically is not that crazy of a use case and you see it pop up pretty often. – Wilson Biggs Jun 6 at 16:06
15

Yes, but it is pretty involved...

Add to functions.php in your theme:

function orderby_tax_clauses( $clauses, $wp_query ) {
    global $wpdb;
    $taxonomies = get_taxonomies();
    foreach ($taxonomies as $taxonomy) {
        if ( isset( $wp_query->query['orderby'] ) && $taxonomy == $wp_query->query['orderby'] ) {
            $clauses['join'] .=<<<SQL
LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
LEFT OUTER JOIN {$wpdb->term_taxonomy} USING (term_taxonomy_id)
LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
SQL;
            $clauses['where'] .= " AND (taxonomy = '{$taxonomy}' OR taxonomy IS NULL)";
            $clauses['groupby'] = "object_id";
            $clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
            $clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
        }
    }
    return $clauses;
}

    add_filter('posts_clauses', 'orderby_tax_clauses', 10, 2 );

This is frankensteined from some found stuff and some stuff I did myself. Explaining is pretty tough, but the bottom line is with this running, you may put ?orderby=(taxonomy query var)&order=ASC (or DESC) and she'll take right off!

  • Thank you Drew, I will give a go and try to run that SQL, need to edit a bit, but it might work. My only problem now, is I might be going on the wrong directions as pointed by Otto. Thank you Drew. EDIT- No need to edit I can see where it needs the tweaking :) Thank you – yeope Apr 8 '11 at 16:45
  • If you grabbed it within the last two minutes, it won't work, go ahead and grab it now, I fixed it. It was set for two specific taxonomies, I improved the code to work on all registered taxonomies. – Drew Gourley Apr 8 '11 at 16:48
  • thank you once again. Just in case I tried your solution and it kind of works. Also if someone else wants to use it you need to change add_filter('posts_clauses', 'orderby_tax_clauses', 10, 2 ); to add_filter('posts_clauses', 'todo_tax_clauses', 10, 2 ); Thank you :) – yeope Apr 8 '11 at 22:29
  • Yes, this is now fixed in the code block, I took this from a project I'm working on and forgot to change the name of the function even though I changed it in the hook. – Drew Gourley Apr 9 '11 at 6:39
  • 1
    Do you know if it's possible to order the taxonomies by ID instead of the name? I'm trying to get the same result ordering the taxonomy groups by ID – Javier Villanueva Jul 16 '13 at 22:43
9

I'm coming late to the game here, but there is a simpler more WordPressy way of doing this.

Build out your tax query like normal.

$tax_query = array();
$tax_query['relation']="OR";
$tax_query[] = array(
    'taxonomy' => 'product_cat',
    'field'    => 'slug',
    'terms'    => $cat_terms,
);
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;

Set up your args for query_posts or WP_Query

$args = array(
    'post_type'=>'post',
    'posts_per_page'=>12,
    'paged'=>$paged,
    'tax_query' => $tax_query,
);

Before you make your query_posts / WP_Query call, hook into the orderby filter and override it

add_filter('posts_orderby', 'edit_posts_orderby');
function edit_posts_orderby($orderby_statement) {
    $orderby_statement = " term_taxonomy_id ASC ";
    return $orderby_statement;
}
query_posts($args);
remove_filter('posts_orderby', 'edit_posts_orderby');

don't forget to remove filter afterwards...

this works b/c the tax_query creates the joins etc for you, you just need to order by one of the fields from the join.

  • 2
    Any idea on how to order by name instead of term_taxonomy_id? changing term_taxonomy_id in orderby_statement throws errors – tehlivi Oct 4 '16 at 21:52
  • This is the correct answer for anyone that is interested! – Mayra M Jun 21 '18 at 10:43
2

Well, I’d like to expose my experience in sorting custom post types by category/taxonomy.

THE WEB

  1. A travel agency web site running on WordPress
  2. Main content on custom post type called ‘ruta’
  3. Taxonomy with this structure Type-of-travel > continent >country

THE CASE

In archive category list pages, the client wanted the posts to be sorted by

  1. The continent, ordered by number of routes on each one.
  2. The country, ordered alphabetically.

THE STEPS

First, I catch the request from the unmodified archive page query which happened to be like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23,25,26,28,29,31,32,33,35,38,95,101,102,193) )
AND wp_posts.post_type IN ('ruta', 'nav_menu_item') 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 45 
AND wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 20

Second, I edited the sql code in Sequel Pro against the database to conform my needs. I come out with this (yes, probably it can be improved: my knowledge on MySQL is not outstanding):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID, tt1.parent AS pare,
    (
    SELECT COUNT(*) 
    FROM  wp_posts
    INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy AS tt1 ON ( tt1.term_taxonomy_id =      wp_term_relationships.term_taxonomy_id )
    INNER JOIN wp_term_taxonomy AS tt2 ON ( tt2.term_taxonomy_id =  tt1.term_taxonomy_id )
    WHERE 1=1  
    AND tt1.parent = pare
    ) AS Total
FROM  wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy AS tt1 ON ( tt1.term_taxonomy_id =      wp_term_relationships.term_taxonomy_id )
INNER JOIN wp_terms ON ( tt1.term_id = wp_terms.term_id )
WHERE 1=1  
AND ( wp_term_relationships.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23,25,26,28,29,31,32,33,35,38,95,101,102,193) ) 
AND wp_posts.post_type IN ('ruta', 'nav_menu_item') 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 45 
AND wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY
total DESC,
wp_terms.name  

Third, I hooked the query on the functions.php file with three filters: posts_fields, posts_join and posts_orderby

The code in functions.php:

function xc_query_fields( $fields ) {

   $fields = "wp_posts.ID, wp_posts.post_title, wp_terms.name, tt1.parent AS pare,
    (
    SELECT COUNT(*) 
    FROM  wp_posts
    INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy AS tt1 ON ( tt1.term_taxonomy_id = wp_term_relationships.term_taxonomy_id )
    INNER JOIN wp_term_taxonomy AS tt2 ON ( tt2.term_taxonomy_id = tt1.term_taxonomy_id )
    WHERE 1=1  
    AND tt1.parent = pare
    )
    AS Total";
     return $fields;
}


function xc_query_joins( $join ) {
$join .= "INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
   INNER JOIN wp_term_taxonomy AS tt1 ON ( tt1.term_taxonomy_id = wp_term_relationships.term_taxonomy_id )
   INNER JOIN wp_terms ON ( tt1.term_id = wp_terms.term_id )";
 return $join;
}


function xc_query_orderby( $join ) {
    $join = "total DESC, wp_terms.name ";
    return $join;
 }

Finally I triggered the filters from the pre_get_post hook according to some conditions

function filtra_queries( $query )
{

  if (  is_archive()  && $query->is_main_query() && !is_admin()  ) {

$rutes = array('viajes-privados', 'asia', 'africa', 'oceania', 'america', 'oriente-proximo');

if  ( in_array( $query->get('category_name'), $rutes ) ) 
  {
  add_filter( 'posts_fields', 'xc_query_fields' );
  add_filter( 'posts_join', 'xc_query_joins' );
  add_filter( 'posts_orderby', 'xc_query_orderby' );
}// end if in_array

  }// end if is_archive

}
 add_filter('pre_get_posts', 'filtra_queries');

Hope this can help somebody

  • Nice work, ridiculous that is took this quantity of code to sort something by a taxonomy. Huge problem with WP. – staypuftman Jul 20 '17 at 23:27
2

I had a very similar problem that I dealt with: I want to order a custom post-type archive (magazine articles) by a custom taxonomy (issues). I never do direct SQL queries on my site - and usually if you are like these other answers - you need to rethink your approach.

PROBLEMS:

1) Wordpress doesn't allow you to order taxonomies in any intelligent way.

2) Wordpress just doesn't allow orderby to be use taxonomies on post-type WP_Query (as spelled out by Otto).

SOLUTIONS:

1) Sorting taxonomies is best accomplished by the Custom Taxonomy Order NE plugin at the moment. It allows you to order the taxonomy via WYSIWYG in wp-admin which isn't how I would do it but I haven't found anything better.

When you setup the plugin, you'll get something similar to what I've done here. Make note of the option Auto-sort Queries of this Taxonomy - set this to Custom Order as Defined Above; this gets you the ordering you need. Screenshot:

Custom Taxonomy Order NE display

2) With a sorted taxonomy in place, you can now create a series of WP_Query calls that run through each term, effectively creating an archive ordered by the taxonomy. Use get_terms() to create an array of all tax terms, then run a foreach over each term. This creates a WP_Query for each term item that will return all posts for a given term, effectively creating an archive ordered by taxonomy term. Code to make this happen:

  // Get your terms and put them into an array
  $issue_terms = get_terms([
    'taxonomy' => 'issues',
    'hide_empty' => false,
  ]);

  // Run foreach over each term to setup query and display for posts
  foreach ($issue_terms as $issue_term) {
    $the_query = new WP_Query( array(
      'post_type' => 'post',
      'tax_query' => array(
        array(
          'taxonomy' => 'issues',
          'field' => 'slug',
          'terms' => array( $issue_term->slug ),
          'operator' => 'IN'
        )
      )
    ) );

    // Run loop over each query
    while($the_query->have_posts()) :
      $the_query->the_post();

      // YOUR TEMPLATE OUTPUT FOR EACH POST

    endwhile;
  }

Related reading on this site: Display all posts in a custom post type, grouped by a custom taxonomy

1

Here's the solution I've used for this particular problem. This solution is for extreme cases where it is both not possible to use a pre_get_posts filter and there is existing pagination on the query (ie: WooCommerce):

global $wpdb;

$taxonomies = array('my-tax-1', 'my-tax-2', 'my-tax-3');

$orderby = "'".implode("', '", array_keys($taxonomies))."'";
$id_sql = $GLOBALS['wp_query']->request;

$id_sql = preg_replace('/LIMIT\s+\d+\s?,?\s\d*/', '', $id_sql);
$id_sql = str_replace('SQL_CALC_FOUND_ROWS', '', $id_sql);

$term_sql = "SELECT
  tt.taxonomy AS `taxonomy`,
  t.name AS `term_name`,
  t.slug AS `term_slug`,
  count(*) AS `term_count`
FROM ({$id_sql}) p 
JOIN wp_term_relationships tr
  ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t
  ON tt.term_id = t.term_id
WHERE tt.taxonomy IN ({$orderby})
GROUP BY t.slug
ORDER BY
  FIELD(tt.taxonomy, {$orderby})"; // Add further specific ordering here

$results = $wpdb->get_results($term_sql, ARRAY_A);

I've used this to create a nav menu ordered by taxonomy, term, and count of posts-per-term.

If you simply want the posts then change the query to SELECT p.* and GROUP BY p.ID

1

I'm not sure why all solutions here are pretty much overkilling it. OK, it's half a decade ago, but I'm just running the following code currently and it works:

   <?php // Default
    $wheels_args = array(
        'post_type' => 'wheels',
        'posts_per_page' => '96',
        'orderby' => 'taxonomy, name', // Just enter 2 parameters here, seprated by comma
        'order'=>'ASC'
    );
    $loop = new WP_Query($wheels_args);
    ?>

This will sort the taxonomies of your CPT first by it's taxonomy in alphabetical order and within these taxonomy groups als by alphabetical order.

0

It's like a query before the query, but won't bother if we're not not querying too many posts... The idea is to modify the main query so we don't even need to go to templates and generate new queries and loops...

function grouped_by_taxonomy_main_query( $query ) {

    if ( $query->is_home() && $query->is_main_query() ) { // Run only on the homepage

        $post_ids = array();

        $terms = get_terms('my_custom_taxonomy');

        foreach ( $terms as $term ) {
            $post_ids = array_merge( $post_ids, get_posts( array( 
                'posts_per_page' => 4, // as you wish...
                'post_type' => 'my_custom_post_type', // If needed... Default is posts
                'fields' => 'ids', // we only want the ids to use later in 'post__in'
                'tax_query' => array( array( 'taxonomy' => $term->taxonomy, 'field' => 'term_id', 'terms' => $term->term_id, )))) // getting posts in the current term
            );
        }

        $query->query_vars['post_type'] = 'my_custom_post_type'; // Again, if needed... Default is posts
        $query->query_vars['posts_per_page'] = 16; // If needed...
        $query->query_vars['post__in'] = $post_ids; // Filtering with the post ids we've obtained above
        $query->query_vars['orderby'] = 'post__in'; // Here we keep the order we generated in the terms loop
        $query->query_vars['ignore_sticky_posts'] = 1; // If you dont want your sticky posts to change the order

    }
}

// Hook my above function to the pre_get_posts action
add_action( 'pre_get_posts', 'grouped_by_taxonomy_main_query' );

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.