Group functionality in server runtime api

Game Design
1

I’m looking at the group api’s this morning, am I right in thinking that there are no server runtime equivalents for ListGroups and ListGroupUsers?
Also, is it only possible to wildcard search on the group name? (not the description?)
I’m porting my GameSparks functionality over and I’ve currently got team tag, name and description. The tag is 3 characters and unique. The name and tag are searchable. All three are editable.
I can put the tag in the name field, and the name in the description field (or metadata) but then I can only search on tag. If I combine the tag and name in the name field then I can’t guarantee a unique tag without a server side search api.
Searching on other fields would be great too. I have numeric fields for round and minimum level which I need to filter on when a player searches for a team to join. I currently do this in server code and return the filtered list to the user. Obviously this is preferable to returning a much bigger list and doing it client side. Doing it in the query would be better still.
For example:
{“round” : 3, “minLevel” : 10}

Sorry for the rambling nature of this question. :slight_smile:

1 Like
2

@DaveOak This is a great question. We spoke briefly over Gitter so I’m glad we can discuss the options in the forums. It’s easier to give examples.

am I right in thinking that there are no server runtime equivalents for ListGroups and ListGroupUsers?

You can find both ListGroups and ListGroupUsers in the server runtime. Have a look for "nk.user_groups_list" and "nk.group_users_list":

https://heroiclabs.com/docs/runtime-code-function-reference/#groups

I can put the tag in the name field, and the name in the description field (or metadata) but then I can only search on tag.

I would suggest you keep the name in the name field of the group structure and put the tag into the group metadata and then can assign a unique index to it inside a run_once function. This requires that you drop under the hood of the main API but can carefully add the additional customizations you need.

NOTE: Never make changes to the database tables in the Nakama schema itself. This could cause all kinds of errors in the queries written for the API built into the game server. Indexes and separate tables if absolutely needed.

nk.run_once(function()
    -- Add a unique constraint to group metadata tag fields.
    local sql = [[
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS groups_metadata_tag_uniq_idx
    ON groups (((metadata->>'tag')::TEXT)) WHERE (metadata->>'tag') NOT IN (NULL, '');
]]
    nk.sql_exec(sql, {})
end)

You can then perform a query over that JSON field in the metadata to efficiently look up on the tag:

SELECT name, description, id, metadata, edge_count FROM groups WHERE (metadata->>'tag') ILIKE 'sometag%'

The use of "ILIKE" (case-insensitive LIKE) and the "%" wildcard is a postfix search on the index which is pretty fast if you want to show results which partially match the “sometag” string prefix. Never use the wildcard at the start of the search as a prefix (i.e. “%sometag”) because this will always result in poor performance.

This is a good article on the subject with ILIKE and indexes:

Index Columns for `LIKE` in PostgreSQL | Niall Burkley's Developer Blog

Searching on other fields would be great too. I have numeric fields for round and minimum level which I need to filter on when a player searches for a team to join. I currently do this in server code and return the filtered list to the user.

This is actually a great solution to the problem. It can be very tricky to provide the right kind of indexes to manage the flexibility required to efficiently scan the groups looking for entries that fit the filter criteria especially as it usually grows more complex over time in games. Your solution to overscan what you need and filter in the RPC function is a good one because the database performance profile will always be great and if you limit the results returned to less than 1000 I imagine you’ll have more than enough to surface a good 20-30 options to the player.

In general we recommend you minimize the use of the raw SQL features of the game server because bad queries can have a huge performance impact on the rest of the system but it can be very very useful when used sparingly.

If you do think we should talk through more how to achieve the more advanced filters you need then give me a detailed example of what sort of filter options you want to provide in the game UI and we can talk it through. :+1:

Hope this helps.

1 Like
3

Very helpful, thanks!

1 Like
4

@novabyte - I am currently failing to create the index using your example SQL. I have upgraded Cockroach to V20.2.0 and I am getting the following error from run_once and also the Cockroach CLI. I’m not sure what to try next. Note the error should be pointing at the close bracket before WHERE. Thanks!

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS groups_metadata_tag_uniq_idx
ON groups (((metadata->>‘tag’)::TEXT)) WHERE (metadata->>‘tag’) NOT IN (NULL, ‘’)
^
HINT: You have attempted to use a feature that is not yet implemented.
See: sql: support expression-based index columns · Issue #9682 · cockroachdb/cockroach · GitHub

5

@DaveOak I see. I’d forgotten that CRDB does not yet support computed indexes in their partial index implementation. In the meantime you can just drop the WHERE clause because its not strictly needed just saves a bit on disk space and apply the workaround described here:

https://github.com/cockroachdb/cockroach/issues/9682?version=v20.2#issuecomment-718664880

You’d created a “STORED” column which is a computed column to extract the metadata tag into a separate column on the table and add a UNIQUE index on that.

i.e.

ALTER TABLE groups
	ADD COLUMN IF NOT EXISTS
		computed_metadata_tag
			STRING AS ((metadata->>'tag')) STORED;

CREATE UNIQUE INDEX IF NOT EXISTS groups_computed_metadata_tag_uniq_idx
	ON groups (computed_metadata_tag);

It’s not ideal but solves your use case and introduces the UNIQUE constraint needed around the tags and makes it easier to efficiently look them up with exact match queries (and ILIKE queries if needed).

1 Like
6

Thanks @novabyte, that’s great. Just a heads up that the example docker-compose.yml installs old versions of both Nakama and Cockroach.

2 Likes
7

Thanks I’ll update the docs to include the latest versions though you can always follow with the latest Docker compose:

https://github.com/heroiclabs/nakama/blob/master/docker-compose.yml

8

Great - why not link that from the docs? One less thing to keep up to date… :smiley:

1 Like
9

Do you have an example of sql_query with placeholders and parameters please?

10

Sure, a quick example of how you could check this code with a SQL query looks like:

local query = [[
SELECT
	id, name, metadata
FROM
	groups
WHERE
	computed_metadata_tag = $1
LIMIT
	1
]]
local parameters = { some_tag_name }
local rows = nk.sql_query(query, parameters)
for i, row in ipairs(rows) do
    nk.logger_info(string.format("Group name %q id %q", row.name, row.id))
end

NOTE: I’ve not tried the code so expect typos. :slight_smile:

1 Like
11

There are also a couple of examples on the function reference in case you find it useful.

12

Thanks for the help! Sorry, I missed the examples with parameters first time through. :frowning: