🚀 Feature: Adding Random option to query

[byawitz]

🔖 Feature description

Adding the option to select n items as random.

The implementation would look something like this

$documents = static::getDatabase()->find('movies', [
    Query::random(),
    Query::limit(5),
]);

Or with limit as the random parameter.

$documents = static::getDatabase()->find('movies', [
    Query::random(5),
]);

Adapter are required for:

• MariaDB
• MySQL
• SQLite
• Postgres
• MongoDB.

🎤 Pitch

I first intend to add it as a feature for Appwrite database endpoints.
Also, It can be helpful in games, for example.

👀 Have you spent some time to check if this issue has been raised before?

• I checked and didn't find similar issue

🏢 Have you read the Code of Conduct?

• I have read the Code of Conduct

[joeyouss]

Hi
Thank you for sharing this. How do you plan to implement this in MongoDB and MariaDB. Having a clear path about implementation might help our engineers understand more and then review PR. That being said, this feature would be a really cool one if implemented!

[byawitz]

Thanks for your replay
Sure thing, I'm attaching basic query (of course I will adapt it).

Postgres

SELECT * FROM users ORDER BY random() LIMIT 5;

MySQL / MariaDB / SQLite

SELECT * FROM users ORDER BY RAND() LIMIT 5;

MongoDB

db.users.aggregate(
   [ { $sample: { size: 3 } } ]
)

[joeyouss]

Thanks for sharing, adding @fogelito for review/feedback on this idea.

[fogelito]

Hello, @byawitz Thanks for sharing new ideas!
So this is possible to do as more community members will need to vote for this feature.
random on big tables is always a table scan so better use on small collections.
So in the meanwhile, with the coming new release you can query with a relatively big limit, and use your client code to random the results fetched.

[byawitz]

Hey, @fogelito. Of course, that can be a slow query in RDBMS, I agree, Though there is a way to mitigate it by using to know the number of rows and adding it as a pre-defined var.

That means that for every insert to the database, a counter of it will be added (and decreased any delete), this can be another great feature for any case.

[fogelito]

@byawitz Correct, As to count all rows in the collection, but in the case of filtering queries, it will be hard to count all options.

[byawitz]

Thanks @fogelito,
Agree. Let's wait for (if any) votes