samedi 19 décembre 2020

Select random row per distinct field value while using joins

I have a Wordpress instance showing some posts. Each post is defined in a specific language and has a property _post_year set. So we can have several posts with the same language and referring to the same year.

MySQL tables:

wp-posts

Contains all posts.

ID | post_author | post_date | ...
==================================
 1 |         ...
 2 |         ...
...

wp_term_relationships

Contains information about a language of a post (amongst other things).

object_id | term_taxonomy_id | term_order |
===========================================
        1 |              ...
        1 |              ...
        2 |              ...
...

wp_postmeta

Contains post meta information (like an additional property "_post_year").

meta_id | post_id | meta_key | meta_value |
===========================================
      1 |       1 |      ...
      2 |       1 |      ...
...

I once was able to load one random post per year (for all years available) like this:

SELECT DISTINCT
    wp_posts.*,
    postmeta.meta_value as post_meta_year
FROM (
    SELECT * FROM wp_posts
    JOIN wp_term_relationships as term_relationships
    ON term_relationships.object_id = wp_posts.ID
    AND term_relationships.term_taxonomy_id IN ({LANGUAGE_ID})
    ORDER BY RAND()
) as wp_posts

JOIN wp_postmeta as postmeta
ON postmeta.post_id = wp_posts.ID
AND postmeta.meta_key = '_post_year'
AND post_status = 'publish'

GROUP BY post_meta_year DESC
ORDER BY post_meta_year DESC

Since i upgraded MySQL to version 5.7 this doesn't work anymore:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wp_posts.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How can i achieve to get a random post per year sorted descendingly?




Aucun commentaire:

Enregistrer un commentaire