I have a custom post type, and am looking to order-by a meta key. I have added the below, which works:

function training_qv_anyx_training($query, $block) {
    if ( 'anyx-training' === $query['post_type']) {
         $query['meta_key'] = "anyx-training-order";
         $query['orderby'] = "meta_value_num";
         $query['order'] = "ASC";
    }
    return $query;
}
add_filter( 'query_loop_block_query_vars', 'training_qv_anyx_training', 10, 2 );

However, when I say it works, its intermittent for some reason (the loop sometimes returns no entries). Using Query Monitor, I find the below is the query generated, and, the error it gives about the non-aggregated order-by

MySQL [www_database]> SELECT wp_posts.ID
    -> FROM wp_posts
    -> LEFT JOIN wp_term_relationships
    -> ON (wp_posts.ID = wp_term_relationships.object_id)
    -> INNER JOIN wp_postmeta
    -> ON ( wp_posts.ID = wp_postmeta.post_id )
    -> WHERE 1=1
    -> AND ( wp_term_relationships.term_taxonomy_id IN (1890041) )
    -> AND ( wp_postmeta.meta_key = 'anyx-training-order' )
    -> AND ((wp_posts.post_type = 'anyx-training'
    -> AND (wp_posts.post_status = 'publish'
    -> OR wp_posts.post_status = 'complete'
    -> OR wp_posts.post_status = 'private')))
    -> GROUP BY wp_posts.ID
    -> ORDER BY wp_postmeta.meta_value ASC;
ERROR 1055 (42000): Expression #1 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What am I doing wrong?

I have a custom post type, and am looking to order-by a meta key. I have added the below, which works:

function training_qv_anyx_training($query, $block) {
    if ( 'anyx-training' === $query['post_type']) {
         $query['meta_key'] = "anyx-training-order";
         $query['orderby'] = "meta_value_num";
         $query['order'] = "ASC";
    }
    return $query;
}
add_filter( 'query_loop_block_query_vars', 'training_qv_anyx_training', 10, 2 );

However, when I say it works, its intermittent for some reason (the loop sometimes returns no entries). Using Query Monitor, I find the below is the query generated, and, the error it gives about the non-aggregated order-by

MySQL [www_database]> SELECT wp_posts.ID
    -> FROM wp_posts
    -> LEFT JOIN wp_term_relationships
    -> ON (wp_posts.ID = wp_term_relationships.object_id)
    -> INNER JOIN wp_postmeta
    -> ON ( wp_posts.ID = wp_postmeta.post_id )
    -> WHERE 1=1
    -> AND ( wp_term_relationships.term_taxonomy_id IN (1890041) )
    -> AND ( wp_postmeta.meta_key = 'anyx-training-order' )
    -> AND ((wp_posts.post_type = 'anyx-training'
    -> AND (wp_posts.post_status = 'publish'
    -> OR wp_posts.post_status = 'complete'
    -> OR wp_posts.post_status = 'private')))
    -> GROUP BY wp_posts.ID
    -> ORDER BY wp_postmeta.meta_value ASC;
ERROR 1055 (42000): Expression #1 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What am I doing wrong?

• mysql
• wordpress
• tidb

1 Answer 1

Thanks to the pointer above, this is the fix:

function training_qv_anyx_training_gb($stmt) {
    $post_type = get_post_type();
    if ($post_type == "anyx-training") {
        return '';
    }
}
add_filter( 'posts_groupby', 'training_qv_anyx_training_gb');