@Mohammed The approach you’ve taken I would not recommend at all. It doesn’t scale well and I think its unnecessary because the chat message system is designed so that you can cache the cursor received when you list chat messages.
When the game client starts up you can check whether you’ve got a cursor cached locally (I’d recommend you use PlayerPrefs) and then list chat message history with the cursor. You catch yourself up to the most recent message and then you just take the count of messages you’ve listed up to; it is the count of unread messages for this user to see.
This will be done for each chat message
Is this how things normally done? Or there is a better approach.
Hmmm I’ve never seen a game which restricts chat messages to some kind of daily limit. I have seen rate limits implemented but never a hard count limit. Are you sure this is right for your game design? The ability for players to freely chat between each other creates great retention in a social game. I think if you limit that ability it could dramatically hurt your retention numbers.
In any case if that’s really what you want to achieve then I think the approach you’re taking would work fine.
read storage => unmarshal json => decrease measages cout by 1 => marshal => store it
I think given the unique requirements I’d just use a JSONB query to do the partial update as a single operation. Let me know if you need some pointers on it.
I’ve never seen a game which restricts chat messages to some kind of daily limit. I have seen rate limits implemented but never a hard count limit. Are you sure this is right for your game design?
I still did not finalize this approach, but you are right about
I think if you limit that ability it could dramatically hurt your retention numbers.
I am trying to make a reasonable limit that usually fine for the normal usage.
so I spent sometime reading about jsonb functions and tried it, i found your answer
the SQL query will look like this for me
UPDATE storage SET value = value
|| jsonb_build_object(‘messageRemainingCount’, COALESCE((value->> ‘s’)::INT - 1::INT, 0::INT))
WHERE collection = ‘PlayerData’ AND user_id = $1 AND key = ‘profile’;
This will only change the count only
can you please help if possible to do the below in one call to the databese