I am creating a passive turn-based strategy game where a user can create a base and attack other player’s bases even when those opponents are offline. Is there a way to get all the users from the database or 1 random user which user id is unknown from the attacker?
Hi @ariap! Databases are designed to store and provide various forms of structured access to data so they’re generally not good at “random” access, but what you’re describing is possible with a little bit of runtime code.
You can generate a random UUID, and use a raw SQL query to select a single user whose ID is after your UUID. If you use the Lua runtime this would look something like this:
nk.sql_query("SELECT id FROM users WHERE id > $1 LIMIT 1", {nk.uuid_v4()})
(This selects just the user ID, but you can retrieve other columns as needed.)
3 Likes
There’s a lot of cleverness in this code. Especially as it appears to be such a simple solution from a SQL perspective.
Nakama uses UUIDv4 types which will be alphanumerically sorted on the btree primary index of the users table. This means that users are spread over a pseudo-random distribution within the index. The query above takes a random UUID and uses that as a “pointer” into the btree index which can be scanned onwards from. This is VERY FAST and also gives the “randomness” needed to matchmake on.
This kind of solution can be built on easily which includes:
- Set the LIMIT to 1,000-2,000 results for the users.
- Use a batch read from those users to fetch storage objects as a single database operation.
- Filter on those parameters as much as needed until you have 10-20 relevant opponents.
This will scale to very large player bases (50M+) and enable you to utilize RAM in the game server where it would otherwise cause a lot of database pressure. (This is a common problem with most game servers that attempt to implement asynchronous matchmaking solutions.)
1 Like
Thank you for your reply, I’ll try to implement it.