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?

Upvotes: 0

Answers (2)

GMB

Reputation: 222482

In MySQL 5.7, where mode ONLY_FULL_GROUP_BY is (happily) enabled by default, I would recommend a correlated subquery for filtering:

select *  -- better enumerate the actual column names here
from wp_posts p
inner join wp_postmeta pm on pm.post_id = p.id
where pm.meta_key = '_post_year' and p.id = (
    select pm1.post_id
    from wp_post p1
    inner join wp_postmeta pm1 on pm1.post_id = p1.id
    where p1.status = 'publish' and pm1.meta_key = '_post_year' and pm1.meta_value = pm.meta_value
    order by rand() limit 1
)

Basically the subquery selects one random post id per group of records having the same '_post_year', which is used to filter the query.

Note that with this technique there is no need to filter again in the outer query on the post status, since the subquery does it already and returns a primary key column.

Upvotes: 1

sticky bit

Reputation: 37472

One method you can try: From a derived table with the distinct years select the year and, in a correlated subquery, a random post ID with that year using ORDER BY rand() and LIMIT 1. Join the result of that second derived table with the posts.

SELECT po1.*,
       ppmo1.meta_value
       FROM (SELECT pmo1.meta_value,
                    (SELECT pi1.id
                            FROM wp_posts pi1
                                 INNER JOIN wp_postmeta pmi2
                                            ON pmi2.post_id = pi1.id
                                 INNER JOIN wp_term_relationships tri1
                                            ON tri1.object_id = pi1.id
                            WHERE tri1.term_taxonomy_id = {LANGUAGE_ID}
                                  AND pmi2.meta_key = '_post_year'
                                  AND pmi2.meta_value = pmo1.meta_value
                            ORDER BY rand()
                            LIMIT 1) id
                    FROM (SELECT DISTINCT
                                 pmi1.meta_value
                                 FROM wp_postmeta pmi1
                                 WHERE pmi1.meta_key = '_post_year') pmo1) ppmo1
            INNER JOIN wp_posts po1
                       ON po1.id = ppmo1.id
       ORDER BY ppmo1.meta_value DESC;

(Untested because schema and sample data weren't given by consumable DDL and DML.)