SQL order by Fails in xmlsf_get_archives when strict mode enabled

[silvios]

When you have sql_mode=ONLY_FULL_GROUP_BY enabled in function "function xmlsf_get_archives( $post_type = 'post', $type = '' ) {"

$query = "SELECT YEAR(post_date) as year, count(ID) as posts FROM {$wpdb->posts} WHERE post_type = '{$post_type}' AND post_status = 'publish' GROUP BY YEAR(post_date) ORDER BY post_date DESC";

This query will fail because the column post_date is not in the select. The solution is to change it to year

$query = "SELECT YEAR(post_date) as year, count(ID) as posts FROM {$wpdb->posts} WHERE post_type = '{$post_type}' AND post_status = 'publish' GROUP BY YEAR(post_date) ORDER BY year DESC";

You will have to fix the monthly version as well, this one would be:

SELECT YEAR(post_date) as year, LPAD(MONTH(post_date),2,'0') as month, count(ID) as posts FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), LPAD(MONTH(post_date),2,'0') ORDER BY year DESC, month DESC`

VS

SELECT YEAR(post_date) AS year, LPAD(MONTH(post_date),2,'0') AS month, count(ID) as posts FROM $wpdb->posts WHERE post_type = '$post_type' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

Its in the same function

[RavanH]

Excellent, thanks for this !