0

I have to select 10 posts that are received ratings greater than 3 points (in a range between 1 to 5), order by the last vote datetime.

Table wp_posts

ID post_title post_content
1 Sample title #1 Nullam quis ante
2 Sample title #2 Nullam quis ante
3 Sample title #3 Lorem ipsum dolor
4 Sample title #4 Lorem ipsum dolor

Table wp_postmeta

meta_id post_id meta_key meta_value
30 1 rating 4
31 1 last_vote 1648730889152
40 2 rating 3
41 2 last_vote 1648730125057
50 3 rating 4
51 3 last_vote 1648821996056
60 4 rating 1
61 4 last_vote 1648835059771

So, i create this WP_Query (i was following documentation and other site)

$args = [
    'post_type' => 'post',
    'posts_per_page'=> -1,
    'post_status' => 'publish',
    'meta_query'        => [
        'relation' => 'AND',
        'rating_clause' => [
            'key'       => 'rating',
            'compare'   => '>',
            'value'     => 3,
            'type'      => 'NUMERIC'
        ],
        'order_clause' => [
            'key'       => 'last_vote',
            'compare' => 'EXISTS'
        ]
    ],
    'orderby'     => ['order_clause' => 'DESC', 'rating_clause' => 'DESC']
];
$posts = new WP_Query( $args );

But in MySQL there is this error message: "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'mt1.meta_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by".

Idk if there is some problem in query args or database

3
  • have you made modifications to the WP table schema? And is your MySQL/MariaDB up to date or is it an older version? It looks like you have sql_mode=only_full_group_by turned on which is a problem, and not something WordPress would have turned on
    – Tom J Nowell
    Commented Sep 6, 2022 at 14:14
  • a very quick search brought up stackoverflow.com/questions/59536954/…, you shouldn't be able to generate those kinds of error message using WP_Query arguments
    – Tom J Nowell
    Commented Sep 6, 2022 at 14:14
  • Hi Tom, no, I didn't make modifications to the WP table schema, but I have inherited this website. So, hi follow this guide and seems work. Thank you!
    – ClodClod91
    Commented Sep 6, 2022 at 14:39

1 Answer 1

1

You have to turn off sql_mode=only_full_group_by on your server via an SQL command

e.g. https://stackoverflow.com/a/36568545/57482

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.