Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

Question

Im trying to order a wp_query by meta_value

Im using the following arguments

array(
  'post_type'      =>'event',
  'posts_per_page' => -1,
  'meta_query'     => array(
  'dates_query' => array(
          'key' => 'dates',
          'value' => date(time()),
          'compare' => '>='
          )
       ),
  'orderby'        => 'dates_query',
  'order'          => 'ASC',
  'paged'          => 1
);

This is the request that is generated by wp_query

SELECT wp_posts.* FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'dates' 
         AND wp_postmeta.meta_value >= '1514960717' ) ) 
         AND wp_posts.post_type = 'event' 
         AND (wp_posts.post_status = 'publish' 
         OR wp_posts.post_status = 'completed' 
         OR wp_posts.post_status = 'acf-disabled') 
         GROUP BY wp_posts.ID 
         ORDER BY CAST(wp_postmeta.meta_value AS CHAR) ASC

Unfortunatly the results are wrong and when trying to execute the query manualy im getting the following error:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains non-aggregated column

I know there is an option to disable "only_full_group_by" but im wondering if that is the best practice in this case

Thanks

Why are you aggregating with `GROUP BY` in the first place? You never actually use it in your query. Without seeing anything to the contrary, I would recommend that you get rid of `GROUP BY`. — Tim Biegeleisen, Jan 03 '18 at 06:31
I get this error when I run the SQL query in Adminer (which I use for local development). If I run it in phpMyAdmin, which is on my web server, I don't get the error. — Gavin, Jun 03 '22 at 06:08

score 0 · Answer 1 · answered Jan 03 '18 at 06:50

Not sure why you have used data_query But You should try by directly meta_query on key-value compare. Let me know if it works.

array(
    'post_type'     => 'event',
    'posts_per_page'=> -1,
    'paged'          => 1,
    'meta_key'      => 'dates',
    'orderby'       => 'meta_value',
    'order'         => 'ASC',
    'meta_query'    => array(
            array(
                'key'       => 'dates',
                'value'     =>  date(time()), //<-- Cross check if your date has same format.
                'compare'   => '>=',
                'type'      => 'DATE'
            )       
    )               
);

score 0 · Answer 2 · answered Jan 03 '18 at 08:17

I can't comment on wp_query, but, for reference, here's an example of a valid query (although I can't imagine why you want to sort integers as if they were strings):

SELECT DISTINCT p.* 
  FROM wp_posts p
  JOIN wp_postmeta m
    ON p.ID = m.post_id
 WHERE m.meta_key = 'dates'
   AND m.meta_value >= '1514960717'
   AND p.post_type = 'event'
   AND p.post_status IN('publish','completed','acf-disabled')
 ORDER 
    BY CAST(m.meta_value AS CHAR) ASC;

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

2 Answers2