Make tRPC procedure for user and tournament searching

[L-Mario564]

Issue #23 and #37 has to be addressed before doing this issue.

Why?

Any user should be able to search for other users and any published tournaments.

How?

The input would be a string, and the procedure would return an array of users and an array of tournaments.

Searching for users

Each user must return: User.id, User.osuUserId, OsuUser.username. The user must be able to search users using the following fields:

Only if exact match ({field} = {search_str}):

• User.id
• User.osuUserId
• User.discordUserId

Relative search ({field} ilike '%{search_str}%'):

• OsuUser.username

Conditions:

• Users must be ordered alphabetically by osu! username, ascending.
• Banned users should not show up in the results.
• 10 results limit.

Searching for tournaments

Each tournament must return: Tournament.url_slug, Tournament.name, Tournament.acronym, Tournament.logoMetadata, Tournament.bannerMetadata. The user must be able to search tournaments using the following fields:

Only if exact match ({field} = {search_str}):

• Tournament.id

Relative search ({field} ilike '%{search_str}%'):

• Tournament.name
• Tournament.acronym
• Tournament.url_slug

Conditions:

• Tournaments must be ordered alphabetically by name, ascending.
• Tournaments marked as deleted should not show up.
• Tournaments that aren't public yet should not show up.
• 10 results limit.

Other considerations

Apply database indexes if needed to improve query performance.

[ArtemOsuskyi]

Tournaments that aren't public yet should not show up.

It seems tournaments don't have any field to define if they're public or not. How should it be handled?

[L-Mario564]

It seems tournaments don't have any field to define if they're public or not. How should it be handled?

The Tournament table has a dates field which is JSON. The structure for that JSON has it so it's an object that has a publish property which is supposed to store the timestamp (in milliseconds) in which the tournament will be public, meaning that if the publish date is in the past, then it means that the tournament has become public. I made a server util function for handling past and future dates within queries.

[L-Mario564]

Might consider moving the publish and conclude dates into their own separate fields in the Tournament table, to make indexing those fields easier and also querying them easier.

[ArtemOsuskyi]

Yeah it sounds more logical to have them separated. Let's go with that

[L-Mario564]

Alright, I'll create a separate issue for that then, and we'll solve that one first before this one.