get_adjacent_post() doesn't return posts with the same date

[willybahuaud]

When using get_adjacent_post() on a post, the query skips posts with exactly same date and time.
In a blog context, the problem is rarely encountered, but it's more present when posts are added programmatically.

[willybahuaud]

This patch include posts with same datetime in the query, exclude the current post, and do a second order by ID

[jorbin]

Since the get_adjacent_post queries can be some of the worst performing queries, I'd like to see some performance metrics here to make sure we aren't making the queries worse by fixing this edge case.

I'd also like to see some automated tests for this to ensure if we fix it we don't see it crop up again.

[tyxla]

Adding a unit test

[tyxla]

I've submitted a unit test, which tests again both adjacent directions.

[jessedyck]

I implemented this fix using the where and sort filters (to be sure, I also tested by making the same changes as in the diff). It has one issue - because we're saying 'greater/less than or equal to', we don't get an end or beginning to the pagination. We always find a post that matches 'or equal to'.

To get around that, I'm adding 'p.ID > $post->ID' or 'p.ID < $post->ID' to the WHERE clause instead of 'p.ID != $post->ID' so that we get a start/finish to pagination. I don't know if that's the best method for everyone though.

This impacted me in WooCommerce, which is probably a common scenario for this; I used a plugin to import product data from a spreadsheet into WC, which meant most of the post creation dates where the same.

[julianoe]

Hi all ! What's the status on this?

[willybahuaud]

Replying to julianoe:

Hi all ! What's the status on this?

The last patch by @wonderboymusic seem ok. Waiting approval to be merged on core :)

[boodaah]

This issues still exists in version 5.2.x -- and it was reported on ticket #42936 also. The fix above by wonderboymusic needs to be adjusted slightly to work with the updates to the core since. Something like this...

src/wp-includes/link-template.php
-1821 $sort = apply_filters( "get_{$adjacent}_post_sort", "ORDER BY p.post_date $order LIMIT 1", $post, $order );
+1821 $sort = apply_filters( "get_{$adjacent}_post_sort", "ORDER BY p.post_date $order, p.ID $order LIMIT 1", $post, $order );

With the speed of servers now, it's quite easy to have multiple new posts created in the same second, so the sort order needs to have additional sort information to create a unique sort and the ID is a logical source for a unique sort.

This might seem like a small change but at least these other functions rely on the same line of code: get_adjacent_post, get_previous_post, get_next_post, get_adjacent_post_rel_link, adjacent_posts_rel_link, next_post_rel_link, prev_post_rel_link, get_previous_post_link, get_next_post_link, get_adjacent_post_link, adjacent_post_link