Wrong date for next event showing in archive (group by series)

[Owen Morris]

My archive page is set to show events grouped by series, but is not always getting the correct date for the next occurrence. I have checked the query, and the relevant tables and it seems that the Group By clause is causing the Order By clause to have no effect (see https://stackoverflow.com/questions/6734276/how-to-combine-group-by-order-by-and-having and https://mariadb.com/kb/en/library/group-by-trick-has-been-optimized-away/) – my server is running MariaDB, but according to the specs the same issue could occur in mySQL in future updates.
Can the query be updated so that the order by clause is moved to the subquery?
(I wrote a much more detailed version of this question, but had a “no access” error when I tried to submit it!)

[Stephen Harris]

Hi Owen,

Sorry your original post was blocked. Sounds like it was an overactive filter, possibly interpreting it as an attempted MySQL injection.

What version of EO are you running. It should already do an ORDER BY in the subquery: https://github.com/stephenharris/Event-Organiser/blob/develop/includes/event-organiser-archives.php#L351 – or have I misunderstood?

[Owen Morris]

I have EO 3.6.2 and EO-Pro 1.11.11. (PHP 7.2, SQL 10.0.31-MariaDB-cll-lve)
You are right, there is already an order-by clause in the subquery, but it seems from the links I posted that the main query order by will have no effect on the group by, with the group by effectively picking a random row from the grouped results, not the first row.
the SQL query ($wpdb->queries) is https://pastebin.com/6P9mjWQ1

this returns for example the row:

552     94  2016-06-02 21:06:48 2016-06-02 21:06:48 Stalybridge Artisan Market is held indoors at the ...   Stalybridge Artisan Market  Stalybridge Artisan Market is held indoors at the ...       publish closed  closed      stalybridge-artisan-market          2018-01-19 22:27:04 2018-01-19 22:27:04     0   http://localhost/?post_type=markets&#...    103 event       0   1018    1018    2018-12-09  09:00:00    2018-12-09  14:00:00    20

but that event actually has the following occurrences listed in the wp_eo_events table:

998     552 2017-02-12  2017-02-12  09:00:00    14:00:00    1   
999     552 2017-03-12  2017-03-12  09:00:00    14:00:00    2   
1000    552 2017-04-09  2017-04-09  09:00:00    14:00:00    3   
1001    552 2017-05-14  2017-05-14  09:00:00    14:00:00    4   
1002    552 2017-06-11  2017-06-11  09:00:00    14:00:00    5   
1003    552 2017-07-09  2017-07-09  09:00:00    14:00:00    6   
1004    552 2017-08-13  2017-08-13  09:00:00    14:00:00    7   
1005    552 2017-09-10  2017-09-10  09:00:00    14:00:00    8   
1006    552 2017-10-08  2017-10-08  09:00:00    14:00:00    9   
1007    552 2017-12-10  2017-12-10  09:00:00    14:00:00    10  
1008    552 2018-02-11  2018-02-11  09:00:00    14:00:00    10  
1009    552 2018-03-11  2018-03-11  09:00:00    14:00:00    11  
1010    552 2018-04-08  2018-04-08  09:00:00    14:00:00    12  
1011    552 2018-05-13  2018-05-13  09:00:00    14:00:00    13  
1012    552 2018-06-10  2018-06-10  09:00:00    14:00:00    14  
1013    552 2018-07-08  2018-07-08  09:00:00    14:00:00    15  
1014    552 2018-08-12  2018-08-12  09:00:00    14:00:00    16  
1015    552 2018-09-09  2018-09-09  09:00:00    14:00:00    17  
1016    552 2018-10-14  2018-10-14  09:00:00    14:00:00    18  
1017    552 2018-11-11  2018-11-11  09:00:00    14:00:00    19  
1018    552 2018-12-09  2018-12-09  09:00:00    14:00:00    20

It seems that removing the SQL_CALC_FOUND_ROWS from the query leads to the correct result, but would probably cause pagination to fail!

[Owen Morris]

any further thoughts on this? I’m still getting events showing up effectively out of order as the system is picking the wrong occurrence as the next date, often showing a December date for monthly events!

[Stephen Harris]

Hi Owen,

I’ve been looking into this and I’ve made a commit which I think resolves the issue. Given that the bug is not consistently evident, and I wasn’t able to replicate it myself, I can’t be certain it actually resolves the issue, but it does pass the existing tests.

Before I release an update I want to write a few more tests just to check it matches expected behaviour, however you could always apply the patch yourself in the mean time: https://github.com/stephenharris/Event-Organiser/commit/2b814d3ff863615e19573effed3ddcff5fbad56c

[Owen Morris]

That seems to have done the trick – now the only events appearing in Nov/Dec on the standard archive page are the Christmas ones that don’t run through the year 🙂

[Stephen Harris]

Thanks for confirming that Owen,

Pending further tests I’ll include this in the next release.

[Author]

Posts