I’m not 100% sure I understand what’s being asked here, but the code for handling new items is here: ifdb/newitems.php at main · iftechfoundation/ifdb · GitHub
In particular, in getNewItems()
there are a bunch of kinds of things we can put in the “New on IFDB” section:
- Site news
- Games
- Recommended lists
- Polls
- Reviews
- Competitions
- Clubs
- Game News (news added to a particular game listing)
- Competition News
- Club News
And the vast majority of those are sorted by their created
date. The only exceptions are site news, which is sorted by posted
, and reviews, which have the embargo system.
For reviews, the date used for sorting is:
greatest(reviews.createdate,
ifnull(reviews.embargodate, '0000-00-00')) as d,
You have to read this inside out. We’re comparing two dates, the createdate
of the review, and the embargodate
. We’ll sort by whichever is greatest
(i.e. the latter of those two dates).
If the embargodate
is null (i.e. if the review doesn’t have an embargo date), then we’ll pick the greatest
date of either createdate
or 0000-00-00
. Since no review could possibly have a createdate
that old, that means that we’ll use the createdate
for unembargoed reviews.
(This is why if you unembargo a review, it will sort in its createdate
order. If that’s weeks old, then the review will not appear on the home page.)
The sort order in the event of a tie is “undefined”
We’re ultimately sorting all of the items in PHP with usort
. According to PHP’s documentation, If two members compare as equal, they retain their original order. Prior to PHP 8.0.0, their relative order in the sorted array was undefined.
We’re on PHP 7.4, which means that the sort order is “undefined,” which means that PHP can just do whatever it wants with the result. The effect will typically be stable for only as long as nothing gets added to the list of things to sort, but once something new gets added to the list, the items can seem to swap randomly.
(And even when we upgrade to PHP 8.0+, the sort order returned will be the “original” order, which means the order that our database returned them. SQL databases are allowed to return items in an arbitrary order when the ORDER BY
clause is a tie, though I’ve heard a rumor that our database, MariaDB, returns rows in “insertion order” in case of a tie, i.e. createdate
will effectively break the tie.)
For createdate
, you’ll almost certainly never see a tie, because those dates are full timestamps, e.g. 2022-12-10 17:37:20
. But embargo dates are dates timed at midnight; any two reviews embargoed for the same date will hit a tie, and so they’ll return in an “undefined” order, “moving around” non-deterministically, as Mike noticed.