Randomize an Obsidian Dataview Query

What I’m trying to do

I’m trying to build a dataview query that will randomize a limited list of bullet point items. This list is pulling from throughout my vault. I would also like this query to pull a new list every time it refreshes.

As a warning, I have very little coding experience. I’m learning little by little, but my queries are mostly cobbled together from information I’ve found online. I’m probably trying to do this in the dumbest way possible and take constructive criticism.

Things I have tried

I have tried using the generic “Sort rand” and “Limit 3” dataview features. However, this is still grouping the bullet points by the file they originated from and does not refresh when I reload the page.

My current Query:

TABLE item.text

FROM "18 Learning/00 Storage/02 Media Pages"

FLATTEN file.lists as item
WHERE !item.task 
WHERE contains(item.text, "⇈") 


What this gets me:

(These items are all from the same album, and the first three bullet points from that album)

This is how my song lists are set up:

The trick to making something random is to sort by something random first, and that randomiser function needs to change. One of the easier ways to make it change is to add a little bit of the current time. The next part I like to do, and I maybe thinking too much on this, is to make a modulo operation to extract just the latter part of whatever number we’ve arrived at. This probably doesn’t make any sense, but try out the query below and see if it’s random enough for you:

TABLE item.text, item.position.start.offset, item.position.end.offset, date(now).second, rand - (trunc(rand / 83) * 83) 
FROM "18 Learning/00 Storage/02 Media Pages"

FLATTEN file.lists as item
WHERE !item.task AND contains(item.text, "⇈")

FLATTEN (item.position.start.offset * 7 + item.position.end.offset * 13  + date(now).second * 51 ) as rand
SORT rand - (trunc(rand / 83) * 83)
LIMIT 3    

This utilises the start and end offset into the file of the item in question and the current second part of the time multiplied by some prime numbers. We then sort on the reminder of that number divided by another prime number, 83. You could/should try changing these numbers ever so slightly, if you feel it’s repeating it self somehow.

This random generator should then produce a number which is unique for every item (aka song) based on its position in the file. This number will be fixed unless you change the file itself. On top of that number we add the changing bit using the seconds of the time. And these in combination should produce a seemingly random list of songs.

Could it be done simpler? Surely… — Is it better this way? Maybe? — Does it look a little random? I think so…

This is fantastic. I’m going to have to do some research to fully digest how this is working, but the code snip itself is doing exactly what I want it to. Thank you so much!

On the side, and certainly not essential, is there a way to hide the extra sections in the dataveiw table that this snip generates?

They can be deleted, they’re there just for debugging purposes, and to show which values are used to generate the random number.

The key to the solution is the FLATTEN ... as rand, and the SORT ... . So the columns can be removed.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.