Add ability to sort by random

[botzill]

Currently, I want to get a random item from the collection, can we do that?

I tried sort=? but seems to not work.

[nazariydj]

It is a feature that I would need too, maybe with the possibility of passing a seed to maintain pagination.

[flayks]

@benhaynes @rijkvanzanten Is it something that you guys think to implement in a near future? That would be very dope 🙏

[benhaynes]

@directus/team — in the meantime, can we at least get some visibility into the vendors that don't have support for this? Just a list/notes here will work.

[erondpowell]

Hey could this logic be handled on the frontend? How many Items are we talking about?
Perhaps you could use the min/max aggregator to get the first and last Id in the Collection.
Then use a random function that gets an Item (or Items) in-between and including those Ids.

Would this be an option for your use-case?

[rijkvanzanten]

Hey could this logic be handled on the frontend?

Not really, as you would have to fetch all items in order to properly randomly sort.

How many Items are we talking about?

Hundreds of millions potentially

Perhaps you could use the min/max aggregator to get the first and last Id in the Collection.

Primary keys aren't guaranteed to be in any order, so you can't extract first/last just by ID alone 🙂

[erondpowell]

I thought our min/max functions extracted the largest and smallest values, not first and last.
And our default ID is auto-incremented integer (I know we have other options)

In my mind, in certain cases (using int as id and LESS THAN hundreds of millions of items) this might work... Or is my thought process just straight up noob-level?

[rijkvanzanten]

I thought our min/max functions extracted the largest and smallest values, not first and last.

That's correct, but in this case, it wouldn't be a reliable set of information, as it doesn't give us any guarantees on what items exist in between. Even with auto-incremented values, it's not guaranteed that every number in between still exists (as deleting rows will cause "gaps" in the order).

In my mind, in certain cases (using int as id and LESS THAN hundreds of millions of items) this might work.... Or is my thought process just straight up noob-level programming, full stop?

In order to randomly generate IDs to fetch, you need to know what IDs exist in the first place (otherwise you end up requesting items that don't exist, causing other issues). However, if you can't reliably know what items exist by predefined "knowledge", you'll have to first read all available item IDs, before generating an X long set of random IDs in the set you know exist. At that point, you're required to read every item in the table, which is going to cause issues when you have more than like a 1000 items.

So it's a pretty good idea in theory, but it won't scale too well 🙂

[ptobien]

I thought I'd chime in since I needed this feature as well. For anyone interested this is my solution using two queries. This only works if you need exactly one random item (as requested in the OP):

• 1st query: get count of items in (filtered) table (&aggregate[count]=*)
• on the frontend: generate a random index between [0, count)
• 2nd query: select items but offset by the random index and limit by 1 (&offset={index}&limit=1)

I believe on large tables this is performing better than a sort by RANDOM() as the latter generates a random value for each entry in the database. Depending on your needs, you could also cache count and update it periodically.

If you need more than 1 item I think you can still do what @erondpowell suggested - more or less. Let's say you need X items. If you generate exactly X random ids between min and max you'll likely have issues as mentioned by @rijkvanzanten. But if you generate a lot more than X ids (e.g. 10*X ids) and query with a ".. where id in {ids}" (I believe something like filter[id][_in]={ids}) and limit to X, you should statistically get X random items. Obviously this depends on the amount of missing ids in your table.

[br41nslug]

I've given it a shot to implement this in an extension 🤓 https://github.com/br41nslug/directus-extension-randomized

[rijkvanzanten]

Heya! Thanks for opening this feature request!

This feature request has received over 15 votes from the community. This means we'll move this feature request to the Under Review state!

The Core team will schedule a meeting to review this request as soon as possible. The discussion will then be approved or denied. You may or may not be invited to join this meeting with the core team.

For more information, see our Feature Request Process.

[Sandros94]

I've given it a shot to implement this in an extension 🤓 https://github.com/br41nslug/directus-extension-randomized

I would like to report here some consideration I've made while checking out that nice extension from @br41nslug.

While we all agree that the best approach to this would be to use some database native functions (eg.: `ORDER BY RANDOM()' for pg) we also know that not all database types have these type of function, so external one needs to be created. But those external ones have one thing in common: they usually need to know the entirety of the data to shuffle the array.

When shuffling an array one of the first solution that comes in mind is to create an empty array, pick an item at a random index, move it to the empty array and removing it from the original one via something like .splice. Resulting in a quadratic performance of $O(n^2)$ ($n$ being the number of items in the collection). But if we do an in-place shuffling, by moving the randomly picked item to the beginning/end of the array and subtracting by one the array.length each time we do this, we gain a much better linear performance of $O(n)$. Becoming an essential optimization for mid-to-big collections with possibly way more of thousands of records.

Another rather small, but important topic is about shuffling a limited portion of a particular collection. The first intuition could be to fetch all the available ids and pick randomly a subset up to the desired amount, and then fetch a second time the actual data with that array of those randomly picked ids (this way it will go easier on relationships). This second fetch should also be randomized, and the reason being is easier to understand if we see the collection as an array of all the letters in the latin alphabet, When randomly picking ids we are effectively randomly picking single letters, but once we fetch the actual data using those ids we do receive a sorted (based on collection preferences) array. This will result in a lower performance, but still, if we use in-place shuffling for both random picking and final shuffling, we should see performance between $O(amount*n)$ and $O(n^2)$ ($amount$ being our limit: amount and $n$ the total number of items in the collection).

A practical use of this, with some more information, could be checked in this PR.

Things that still need an answer/research:

• How to deal with filtered and limited shuffling (UPDATE: just discovered that internally this shouldn't be an issue)
• How to deal with pagination (probably just a seed to generate a particular randomization that is preserved between pages)

[rijkvanzanten]

The first intuition could be to fetch all the available ids and pick randomly a subset up to the desired amount, and then fetch a second time the actual data with that array of those randomly picked ids (this way it will go easier on relationships)

I don't think this will scale. If you have a database in the millions of items, you're fetching and shuffling a lot of data just to randomize the set. To add to that, WHERE IN queries with more than a handful of primary keys can be very slow in some databases

[Sandros94]

I don't think this will scale.

Me neither

If you have a database in the millions of items, you're fetching and shuffling a lot of data just to randomize the set. To add to that, WHERE IN queries with more than a handful of primary keys can be very slow in some databases

Exactly! Those I've described are just workaround for those database vendors that do not have support for this task. But being just workarounds I consider them ok for an extension but not good enough as Directus first party functions.

The inconvenient part is that, humanly speaking, the randomness of an array is just an non-expected order, but to effectively do it you must have an idea of the entirety of the dataset, or at least of that element that is perceived as the ordering value (either being sequential ids or sort field). In this case we could use that element that we already know it will be in that particular, randomize that order (maybe with a seed to handle pagination) and then do a single fetch to the related data. Effectively making that element the id of the item.
But again, this would scale linearly only in the first array. For example if we want to shuffle the order of the question in a quiz we can do this with a linear performance, but then if we want to also shuffle the multi choice answers of each question we already landed on a quadratic scale. Each key (or ordering element) we add increases exponentially the processing cost and time.

I even find #13041 easier to deal with compared to this one...

[Sandros94]

just noticed that I've created a comment instead of reply. I hate that github does this when you click Quote Reply 🤬