If your custom query filter for ORDER BY is causing issues with GROUP BY in WordPress, it’s likely due to how MySQL handles GROUP BY in relation to ORDER BY. Here’s how you can fix it:
Common Issue
When you use GROUP BY in a WordPress custom query and try to sort it with ORDER BY, MySQL may throw an error like:
“Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column.”
This happens because MySQL’s STRICT mode enforces that the ORDER BY clause must contain columns from the GROUP BY clause or use aggregate functions like MAX(), MIN(), etc.
Solutions
1. Use Aggregate Functions
Modify your ORDER BY to use an aggregate function, such as:
ORDER BY MAX(meta_value) DESC
Example in WP_Query:
1 2 3 4 5 6 7 8 9 | $args = array('post_type' => 'product','posts_per_page' => 10,'meta_key' => 'price','orderby' => 'meta_value_num','order' => 'DESC','fields' => 'ids',);$query = new WP_Query($args); |
2. Modify GROUP BY with ORDER BY Using ANY_VALUE()
If using MySQL 5.7+ with STRICT mode enabled, wrap the non-grouped column in ANY_VALUE().
Example:
1 2 3 4 5 6 7 8 | global $wpdb;$results = $wpdb->get_results("SELECT post_id, ANY_VALUE(meta_value) as priceFROM $wpdb->postmetaWHERE meta_key = 'price'GROUP BY post_idORDER BY price DESC"); |
3. Disable ONLY_FULL_GROUP_BY Mode
If you’re on a local or test environment, you can disable ONLY_FULL_GROUP_BY in MySQL:
1 2 3 | SET sql_mode = ''orSET SESSION sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')); |
Caution: This is not recommended on production servers unless necessary.
4. Ensure Consistency Between GROUP BY and ORDER BY Columns
Make sure all ORDER BY columns are also in GROUP BY.
Example:
1 2 3 4 5 6 7 | $results = $wpdb->get_results("SELECT post_id, meta_key, MAX(meta_value) as priceFROM $wpdb->postmetaWHERE meta_key = 'price'GROUP BY post_id, meta_keyORDER BY price DESC"); |
Final Thoughts
Best Approach: Use MAX() or ANY_VALUE() when necessary.
For Complex Queries: Consider running two separate queries—one to get grouped results, another for ordering.
Avoid Disabling STRICT Mode unless it’s a last resort.