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 used some of this code from this page on Stack Overflow: Using wp_query is it possible to orderby taxonomy?

It's about the function to be able to sort a query by a taxonomy value. Although it works perfectly, I would want to go one step further.

The value I retrieve from the taxonomy term is a number. If I sort by that number, however, it gives this order: 1 10 11 2 21 22 and so on.

I want it to be sorted 'normally', 1 2 3 5 9 11 15 21 22 etc.

According to google I should add 0 to the value to make it a numeric value, but I don't know where to put it in this code. Somewhere, it should say value + 0. Any idea where I should put it?

It's about this code:

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'] .= ( 'DESC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
        }
    }
    return $clauses;
}

    add_filter('posts_clauses', 'orderby_tax_clauses', 10, 2 );
share|improve this question
    
please explain the GROUP_CONCAT usage on the orderby part –  birgire May 23 '13 at 12:04
    
Actually I can't, it's not my own code. It works perfectly, except it doesn't sort 'naturally' (see intro). The thing I am wondering about is why this wouldn't work: $clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name+0 ASC) "; –  Lars van Peij May 27 '13 at 8:12
add comment

2 Answers

up vote 1 down vote accepted

Your question is more about SQL than WordPress.

If you want to sort your term names as numbers, you can in general try

SELECT * FROM wp_terms ORDER BY name+0 ASC

or

SELECT * FROM wp_terms ORDER BY name*1 ASC

or use CAST:

SELECT * FROM wp_terms ORDER BY CAST(name AS SIGNED) ASC

where you can use SIGNED, UNSIGNED or maybe DECIMAL(10,2), just depending on your numbers.

Update

To test the ordering you can then visit your PHPMyAdmin and run this query:

SELECT  tool_terms.name AS tname, tool_posts.* FROM tool_posts 
LEFT OUTER JOIN tool_term_relationships ON tool_posts.ID=tool_term_relationships.object_id 
LEFT OUTER JOIN tool_term_taxonomy USING (term_taxonomy_id) 
LEFT OUTER JOIN tool_terms USING (term_id) 
WHERE 1=1 
AND tool_posts.post_type = 'landschapselement' 
AND (tool_posts.post_status = 'publish' OR tool_posts.post_status = 'draft' OR tool_posts.post_status = 'pending' OR tool_posts.post_status = 'private') 
AND (taxonomy = 'beheerseenheidnummer' OR taxonomy IS NULL) 
GROUP BY object_id 
ORDER BY tool_terms.name+0 ASC, tool_posts.post_title ASC

to see if it works where I replaced

ORDER BY GROUP_CONCAT(tool_terms.name ORDER BY name ASC) ASC

with

ORDER BY tool_terms.name+0 ASC, tool_posts.post_title ASC

where we order the posts by the taxonomy numeric value, and then the second order is on the post title. I also added the field tool_terms.name AS tname to the SELECT part. This works on my install.

You can also try to replace

$clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
$clauses['orderby'] .= ( 'DESC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';

with:

$clauses['orderby'] = " {$wpdb->terms}.name+0 ";
$clauses['orderby'] .= ( 'DESC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
$clauses['orderby'] = ", {$wpdb->posts}.post_title ASC ";
share|improve this answer
    
$clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name+0 ASC) "; $clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name*1 ASC) "; And the other ones, they all don't work in this situation. –  Lars van Peij May 27 '13 at 8:08
    
can you post the generated SQL query? –  birgire May 27 '13 at 9:38
    
How can I do that? –  Lars van Peij May 27 '13 at 11:11
    
there are many SQL profile plugins out there, for example this one –  birgire May 27 '13 at 11:30
    
SELECT tool_posts.* FROM tool_posts LEFT OUTER JOIN tool_term_relationships ON tool_posts.ID=tool_term_relationships.object_id LEFT OUTER JOIN tool_term_taxonomy USING (term_taxonomy_id) LEFT OUTER JOIN tool_terms USING (term_id) WHERE 1=1 AND tool_posts.post_type = 'landschapselement' AND (tool_posts.post_status = 'publish' OR tool_posts.post_status = 'draft' OR tool_posts.post_status = 'pending' OR tool_posts.post_status = 'private') AND (taxonomy = 'beheerseenheidnummer' OR taxonomy IS NULL) GROUP BY object_id ORDER BY GROUP_CONCAT(tool_terms.name ORDER BY name ASC) ASC –  Lars van Peij May 28 '13 at 9:41
show 4 more comments

Try to make your query look like this one:

"SELECT *, 
CASE WHEN ASCII(RIGHT(`%column_name%`, 1)) > 47 AND ASCII(RIGHT(`%column_name%`, 1)) < 58
THEN LPAD(CONCAT(`%column_name%`,  '-'), 5, '0') 
ELSE LPAD(`%column_name%`, 5, '0') END AS `vsort` 
FROM `%table_name%` ORDER BY `vsort`;"

From this webpage

Note: you’ll want to replace %column_name% with the ACTUAL column name, and %table_name% with the actual table name.

The result would be like one after PHP's natsort() function.

share|improve this answer
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.