Ordering terms by term meta

[eherman24]

With the release of 4.4 and the new term meta functionality, it seems that there is no way to order taxonomies by assigned term meta.

For example, I have a custom post type, with a custom taxonomy and each term has term meta associated to it ('tax_position'). In my instance, the 'tax_position' term meta value is the order that the taxonomies should ideally display in.

Using the filter get_terms_args, there doesn't seem to be a way to pass in a meta_key value. We have an orderby, order and a meta_query argument that we can utilize, but no way to specify a meta_key.

The meta_query argument works as intended, and allows me to limit the returned terms by individual integers or within a range.

Passing in meta_key has no effect.

Here is the snippet I was working with when I encountered this issue:

add_filter( 'get_terms_args', 're_order_taxonomies_by_position', 10, 2 );
/*
*	Re-Order the taxonomies based on the tax_position
*	@since 0.1
*/
function re_order_taxonomies_by_position( $args, $taxonomies ) {
	$args['meta_key'] = 'tax_position';
	$args['orderby'] = 'meta_value_num';
	return $args;
}

Sorting posts by meta is easily do-able using:

$query = new WP_Query( array(
  'meta_key'   => 'position',
  'orderby'    => 'meta_value_num',
  'order'      => 'ASC'
) );

This may be a possible enhancement to the new term meta functionality included in 4.4.

[eherman24]

I also meant to link to the respective file, and lines I was referencing:

​https://github.com/WordPress/WordPress/blob/master/wp-includes/taxonomy.php#L1082-L1106

​https://github.com/WordPress/WordPress/blob/master/wp-includes/taxonomy.php#L1108-L1118

[boonebgorges]

I am not a big fan of the 'meta_key' parameter in our other query methods; I think it's a legacy param from before we had meta_query, etc. I agree that it would be nice to add the ability to sort by the results of a meta query, but I wonder if we could get away with only adding named-key support, as introduced in WP 4.2 or 4.3 (I forget which):

$terms = get_terms( 'my_tax', array(
    'meta_query' => array(
        'mq_clause_1' => array( ... ),
        'mq_clause_2' => array( ... ),
        'relation' => 'OR',
    ),
    'orderby' => 'mq_clause_1',
) );

'meta_key' ends up getting translated to this anyway. Is this too opaque a syntax?

[knutsp]

The named key method has worked well for me when querying for posts. The type of the meta-query let's me sort numeric values stored as numeric strings.

Now, with term meta in place, sorting terms by some meta value should be possible for developers.

[eherman24]

@boonebgorges, I just went through and tested passing in a meta query with the orderby clause and it doesn't seem to return the terms ordered by term meta. I'm not sure it's referencing the _termmeta table properly.

I have terms setup in the dashboard as such:
( Term : Position )
IT : 1
Administration : 2
Community Relations : 3

$terms = get_terms( 'peoplecats', array(
	'meta_query' => array(
		'position_clause' => array(
			'key' => 'tax_position',
			'value' => 0,
			'compare' => '>='
		),
	),
	'orderby' => 'position_clause',
) );
print_r( $terms );

The returned order is:

Administrator
Community Relations
IT

Which seems to still be ordering by 'name' instead of the specified custom meta tax_position.

[boonebgorges]

I just went through and tested passing in a meta query with the orderby clause and it doesn't seem to return the terms ordered by term meta

Right, it's not implemented yet. Thus needs-patch :) If we can get an initial patch, it's definitely doable for 4.5.

[eherman24]

Ah, whoops. I missed that tid-bit. I'll keep an ear out for any updates.

[eherman24]

meta_query support for taxonomy queries

[eherman24]

I took a stab at an initial patch for this ticket. I'm no SQL wizard, but it should be a solid starting point.

In my initial testing, using the above tax_position key (integers), the following cases returned in expected order:

$terms = get_terms( 'taxonomy', array(
   'hide_empty' => false,
   'meta_query' => array(
      'position_clause' => array(
         'key' => 'tax_position',
         'value' => 0,
         'compare' => '>='
      ),
   ),
   'orderby' => 'position_clause',
) );

and

$terms = get_terms( 'taxonomy', array(
   'hide_empty' => false,
   'meta_query' => array(
      'position_clause' => array(
         'key' => 'tax_position',
         'value' => 0,
         'compare' => '>='
      ),
   ),
   'orderby' => 'meta_value_num',
) );

I then altered the data stored in the tax_position term meta to a string instead of an integer (A,B,C etc.) and altered the query to test, and the following returned true:

$terms = get_terms( 'taxonomy', array(
   'hide_empty' => false,
   'meta_query' => array(
      'position_clause' => array(
         'key' => 'tax_position',
         'value' => 0,
         'compare' => '>='
      ),
   ),
   'orderby' => 'meta_value',
) );

I was able to sort in both 'ASC' and 'DESC' order (using 'order' => 'ASC' etc) successfully.

I'm sure some further, thorough testing will need to be done - so additional feedback is invited.

[boonebgorges]

Let's do this for 4.5.

WP_Term_Query is starting to look a lot less bonkers....

[jadpm]

Adding sorting capabilities only by existing meta_query named-keys has one evident problem: you can only sort by a termmeta field that is already on the meta_query. Core added support for this on post queries the other way around: to provide a cleaner way to sort by a custom field already on a meta_query, but keeping sorting capabilities by third meta keys.

Besides that, the provided patch has sereval problems.

First, when using at least one meta_query clause $orderby is overriden to $args['orderby'] or to an empty string. It tehn gets a value only if $args['orderby'] matches a given meta_query key. Otherwise, it can be left empty, rpoducing a broken SQL statement:

SELECT DISTINCT t.*, tt.* FROM wp_dev_terms 
AS t INNER JOIN wp_dev_termmeta ON ( t.term_id = wp_dev_termmeta.term_id ) 
INNER JOIN wp_dev_termmeta AS mt1 ON ( t.term_id = mt1.term_id ) 
INNER JOIN wp_dev_term_taxonomy AS tt ON t.term_id = tt.term_id 
WHERE tt.taxonomy IN ('category') 
AND ( 
		( wp_dev_termmeta.meta_key = 'field-one' 
		AND CAST(wp_dev_termmeta.meta_value AS CHAR) = '1' ) 
	AND 
		( mt1.meta_key = 'field-two' 
		AND CAST(mt1.meta_value AS CHAR) = '1' ) 
) 
name ASC 

Note the final name ASC piece.

Then, there is a typo on line 1384:
$orderby = "ORDER BY CAST(term_meta.meta_value AS term_meta)";
should be
$orderby = "ORDER BY CAST(term_meta.meta_value AS {$primary_meta_query['type']})";

I also tested whether the suggested patch works with more than one meta_query clause, with some interesting results:

• When adding an order_by => 'meta_value' statement, the sorting gets applied related to the first meta_query clause.
• When adding an 'orderby' => 'clause_key' statement, it does not get added at all, since there is no AND term_meta.meta_key = 'whatever meta_query clause key corresponds to that clause_key'.

I think this needs a little more work, and a clear guidance. We should support both meta_key/orderby="meta_value" and orderby="clause_key" formats. I am working on a patch to cover both scenarios.

[jadpm]

I am attaching a refreshed patch which follows the structure used in WP_Query and provides:

• support for 'orderby' => 'meta_value' which will sort by the first meta_query clause fields.
• support for 'orderby' => 'meta_value_num' which will sort by the first meta_query clase field as a numeric value.
• support for 'orderby' => 'meta_value' combined with meta_key which will sort by the referenced field key, no matter wthether there is a meta_query entry for it or not, or even when there are no meta_query clauses at all.
• support for 'orderby' => 'meta_value_num' combined with meta_key which will sort by the referenced field key as a numeric, no matter wthether there is a meta_query entry for it or not, or even when there are no meta_query clauses at all.
• support for 'orderby' => 'clause_key' which will sort by the referenced meta_query clause, if any, checking that passing a non-existing clause key will produce the default sorting.

It also provides unit tests to cover all those cases.

[jadpm]

Add sorting by termmeta to get_terms() - with unit tests

[boonebgorges]

@jadpm Thanks so much for the excellent patch and tests.

[boonebgorges]

I'll have to make a couple small changes to 34996-2.patch​ (most importantly, WP_Meta_Query::get_clauses() doesn't until after get_sql() has been run) but it looks very good otherwise.

[boonebgorges]

In 36485:

Allow get_terms() results to ordered by metadata.

The $orderby parameter of get_terms() now accepts the following values,
related to term meta:

• 'meta_value'
• 'meta_value_num'
• the value of the $meta_key parameter
• any key from the $meta_query array

This brings order-by-meta support for terms in line with post, comment, and
user queries.

As a byproduct of these improvements, $meta_key and $meta_value parameters
have been introduced to get_terms(). They interact with $meta_query in the
same way as in WP_Query and other query classes.

Props jadpm, eherman24.
Fixes #34996.