How could we implement a group search recommendation feature?

Hi, we’re trying to build a group search recommendation that would returns with value stored from group metadata.

Consider a following scenario where we would store a region code on group metadata when we create the group, metadata would something like this {"metadata":"{\"region\": 1}"}

Now when an user try to search, they will filter the group only to group that have the same region as the searching user. Hopefully with user joining a group with the same region code, they’ll be more active since other player on the group also plays on the same time zone.

We’re using cockroach db and use this to add column on the groups table

_, err := db.ExecContext(ctx, `
		ALTER TABLE groups
			ADD COLUMN IF NOT EXISTS
				computed_metadata_region
					STRING AS ((metadata->>'region')) STORED;
		CREATE INDEX IF NOT EXISTS
			groups_computed_metadata_region_uniq_idx ON groups (computed_metadata_region);`)
	if err != nil {
		logger.WithField("err", err).Error("DB exec error.")
		return err
	}

And then querying with this


	params := []interface{}{queryLimit}
    region := 1
    open := 0
	params = append(params, open, region)
	query = `
SELECT id, creator_id, name, description, avatar_url, state, edge_count, lang_tag, max_count, metadata
FROM groups
WHERE 
	((state) = ($2))
AND
	cast ((computed_metadata_region) as INT8) = $3
ORDER BY lang_tag ASC, edge_count ASC, id ASC
LIMIT $1`

	rows, err := db.QueryContext(ctx, query, params...)

but it always returned empty rows, anything from the query that we’re missing?

We ended up doing this query to search if a group is at least half full and not full, and open with this query.

SELECT id, creator_id, name, description, avatar_url, state, edge_count, lang_tag, max_count, metadata
FROM groups
WHERE 
	(id > $2)
AND
	((edge_count) > ($3))
AND
	((edge_count) != (max_count))
AND
	((state) = ($4))
ORDER BY random()
LIMIT $1`

We’re comparing the id with uuid pivot which is just a uuid that we generate before query
uuidPivot := uuid.Must(uuid.NewV4()).String()

as for the searching player needs to have the same region with the group we filter the query results by looking the metadata region and comparing it.

1 Like