Randomly order rows in a Dataview query

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.

Example Query

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

image

Pros of this method

  • 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.

Cons of this method

  • Calculating the random value for each file could possibly be expensive if you have a lot of rows in the query.
6 Likes