How to sort the results of custom WP_Query by category name

WP_Query doesn’t have an orderby of ‘category’ that I’m aware of. Or is this an extra parameter that your plugin deals with?

David.

no, my plugin only deals with taxonomies.
I’d rather sort by taxonomy but I saw that it’s very coplicated.
I tought it would be easier to sort by category name…

Ok, in that case, the issue is that the orderby doesn’t support by category. Here’s a full breakdown of which parameters are accepted… https://codex.www.ads-software.com/Class_Reference/WP_Query#Order_.26_Orderby_Parameters.

Probably the best way around this is to fetch the list of categories and then call wp_query for each category in turn.

David.

Category is just a pre-defined taxonomy, so it is just as complicated as ordering by taxonomy. Separate queries for each category is one way to approach the issue. It’s not ideal for heavily trafficked sites with many categories because multiple queries are computationally “expensive”.

Another possible approach is to order the returned array of all posts by category (or taxonomy) by using PHP instead of SQL. usort() could probably be used. This approach is also “expensive”, just in a different way.

If you have a choice of where the sort criteria is stored, use custom fields or post meta (they are the same thing). You can see in David’s link that ‘meta_value’ and ‘meta_value_num’ are possible orderby parameters. This is the best option if you can make it work.

OK… Thanks for your answers.
Actually, I could use both methods, but I’ll have pagination issues.

Because I run the query, and then I only display the page (a certain number of results per page).

If I start but retrieving the categories, then I’ll have to fetch all the results for all the catogries, and only after, display the page.

Or if I use usort(), I first have to retrieve all the results from wp_query, and do the same thing (only display the page taht the user wants to see).

Using a CF to store the category could be possible, but I’ll have to edit more than 400 posts…

What option would you choose?

I could not choose without knowing more about how you would be using the data.

You could take the time to write a one time PHP script to transfer the categories to post meta. I don’t like redundant data, so unless there’s a reason to maintain the categories, they are best removed after the transfer.

There still is other drawbacks to using post meta, WP does not by default support permalinks with post meta terms, but it’s possible to create the functionality with a rewrite rule and perhaps a rewrite tag. There’s also no convenient template tags or widgets like there are for categories, but these can also be custom coded to have equivalent functionality. Of course, if you are not using these category features, there’s no problem ??

Depending on how you would otherwise use these terms, it’s likely worth writing a specific SQL query that can orderby category or taxonomy terms. Yes, it’s complicated, but so is adapting post meta to act like a category. While it’s possible to alter a WP_Query SQL query to do this, it may be easier to go with $wpdb methods.

Actually I alredy tried to make changes to the query but it’s not working.
At the time, I was trying to sort by a taxonomy called “destination”.
Here’s the code that I added to my functions.php file:

add_filter('posts_clauses', 'posts_clauses_orderby_destination', 10, 2);
function posts_clauses_orderby_destination( $clauses, $wp_query ) {
	global $wpdb;
	//array of sortable taxonomies
	$taxonomies = array('destination');
	if (isset($wp_query->query['orderby']) && in_array($wp_query->query['orderby'], $taxonomies)) {
		$clauses['join'] .= "
			LEFT OUTER JOIN {$wpdb->term_relationships} AS rel2 ON {$wpdb->posts}.ID = rel2.object_id
			LEFT OUTER JOIN {$wpdb->term_taxonomy} AS tax2 ON rel2.term_taxonomy_id = tax2.term_taxonomy_id
			LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
		";
		$clauses['where'] .= " AND (taxonomy = '{$wp_query->query['orderby']}' OR taxonomy IS NULL)";
		$clauses['groupby'] = "rel2.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;
}

Now I had to make some changes to the code, and I’d like to sort by category name, so I guess the code would be more or less the same (as the category is a taxonomy), but it doesn’t seem to be working either…

Do you see what could be the problem of my code?
I must admit that the query is quite complicated…

Thanks for your help.

It’s quite difficult to anticipate what the eventual SQL query would be when looking at a list of clauses. Please hook ‘posts_request’ filter and output the actual SQL query from that hook, then post it here. I’d do this myself, but I’m traveling and will not have access to the proper resources for over a week.

Or if you’d rather wait, I’ll do it myself when I get back. I do have an interest in finding a workable solution for this, as this issue tends to come up from time to time. It’d be nice to have a functional example for reference.

Here’s what I added with WP_Query call:

$custom_query = new WP_Query($args);
echo "<p>Last SQL-Query: {$custom_query->request}</p>";

So it gives me the SQL query. Now I’m going to have a look when I get some time, and I’ll try to do something smaller – because the actual query is quite big and complicated. I’m going to try and do a smaller query.

I’ll get back to you as soon as I can.

Thanks

I was unable to make changes to the query, it’s very complicated…
I thought it was ok, I thought I was able to order by category, but when I had a closer look, it was ordered by another taxonomy, and I don’t know why.

Here’s what I did:

add_filter('posts_clauses', 'posts_clauses_orderby_category', 10, 2);
function posts_clauses_orderby_category( $clauses, $wp_query ) {
	global $wpdb;
	if (isset($wp_query->query['orderby']) && ($wp_query->query['orderby'] == 'category')) {
		$clauses['join'] .= "
			INNER JOIN {$wpdb->term_taxonomy} AS wptt
			ON wptt.term_taxonomy_id = tt1.term_taxonomy_id

			INNER JOIN {$wpdb->terms} AS wpt
			ON wpt.term_id = wptt.term_id
		";
		$clauses['orderby']  = "wpt.name ";
		$clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
	}
	return $clauses;
}

If I can’t find any solution, I’ll have to rewrite the code for the plugin, but I’m not very interested in doing it, as the plugin is working fine.
If I rewrite it, there are chances to get some bugs.

I think I should be able to do it. I’m going to go out for a walk and try when I get back. On your side, did you have a chance to have a look at it ?

Sorry for the slow reply. I was hoping someone better at SQL would jump in ?? I’m not convinced the ‘category’ orderby parameter would make it through to this point because it is not an accepted parameter. Or have you confirmed that it’s still there when your callback executes? You may need to manage when the filter is applied differently. One way is to only add the filter callback (without the conditional) in code where the category still exists, such as an action that fires much earlier. ‘pre_get_posts’ or ‘parse_query’ for example? Or even ‘request’?

Just before the ‘posts_clauses’ callback returns, it can remove itself from the filter stack to prevent it from influencing other queries.

If you have confirmed that the category parameter is still usable, then there is likely a glitch in how you joined the tables, perhaps tying the join to the wrong ID. Unfortunately, I’m pretty weak with SQL joins and am easily confused by how the taxonomy tables relate, so I’m not much use in sniffing out where the problem might be.

I’d suggest you extract your fully assembled SQL query from the ‘posts_request’ filter and test it in phpMyAdmin’s SQL tab. It’s much easier to see what’s going on and try variations of the query this way. Once you arrive at the proper query here, it’s much easier to work backwards into the proper clauses adjustment. Sometimes just seeing the fully assembled query is enough to see where the problem is with the clauses adjustment. Good luck!