BIG EDIT: I discovered an improvement to this method that overcomes most of the cons of the previous strategy. Look at the post history if you’d like to see the original.
This is a way to order rows in a DQL query in a pseudorandom order. To do this, we generate a pseudorandom value for each file and then sort by that value.
To generate a pseudorandom value for each file, we start with building a hash for the file, using fields that change often as inputs, for example the file modification time, file size, and the time the query was executed. Then we employ a simple generator to create a pseudorandom value between 0 and 1. We can then sort by this value to put the list in an apparently random order.
This query shows the 10 most recently updated files in random order.
TABLE Rand SORT file.mtime DESC LIMIT 10 FLATTEN date(now) as Now FLATTEN (file.mtime.year + file.mtime.hour + file.mtime.day + file.mtime.hour + file.mtime.minute + file.mtime.second + file.size + Now.hour + Now.minute + Now.second) * 15485863 as Hash FLATTEN ((Hash * Hash * Hash) % 2038074743) / 2038074743 as Rand SORT Rand
- Rows sorted by this field will appear in an apparently random order. There’s no obvious relation between the random value and any one of the file’s fields.
- The rows should not spontaneously re-order unless one of the files has changed.
- The order of rows can be re-shuffled by reloading the page, since the time the query executed will have changed.
- Calculating the random value for each file could possibly be expensive if you have a lot of rows in the query.