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