Displaying chat messages of punished users only to themselves

Question

I have prepared a simple test case for my question.

In an online word game for 2 players based on PostgreSQL 14.2, when someone is misbehaving I set their "muted" column to "true".

Then the chat messages coming from the punished users should be hidden to everyone else.

Except for the punished users themselves - they should see all the chat messages, so that they do not notice that they are muted and do not create new game accounts.

I already do this trick for their avatars

So I have prepared a simple test case, here are my 4 tables:

CREATE TABLE words_users (
    uid SERIAL PRIMARY KEY,
    muted BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE words_social (
    -- social network id
    sid     text     NOT NULL CHECK (sid ~ '\S'),
    -- social network type: 100 = Facebook, 200 = Google, etc.
    social  integer  NOT NULL CHECK (0 < social AND social <= 256),
    given   text     NOT NULL CHECK (given ~ '\S'),
    uid     integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
    PRIMARY KEY(sid, social)
);

CREATE TABLE words_games (
    gid      SERIAL PRIMARY KEY,
    player1  integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
    player2  integer REFERENCES words_users(uid) ON DELETE CASCADE
);

CREATE TABLE words_chat (
    cid     BIGSERIAL PRIMARY KEY,
    created timestamptz NOT NULL,
    gid     integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
    uid     integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
    msg     text    NOT NULL
);

Then I fill the tables with the test data:

-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100, 'Nice user', 1), ('def', 200, 'Bad user', 2);

-- put these 2 users into a game number 10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);

-- start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');

And finally here is the SQL function that I am trying to improve:

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid    integer,
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_mine  integer,
                out_msg   text
        ) AS
$func$
        -- TODO display messages by muted users only to themselves
        SELECT
                CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c 
        JOIN    words_games g USING (gid) 
        JOIN    words_social s ON s.uid IN (g.player1, g.player2)
        WHERE   c.gid    = in_gid
        AND     s.social = in_social
        AND     s.sid    = in_sid
        ORDER BY c.CREATED ASC;

$func$ LANGUAGE sql;

SELECT words_get_chat(10, 100, 'abc') AS nice_user;

SELECT words_get_chat(10, 200, 'def') AS muted_user;

Currently the stored function displays all chat messages, but I would like to hide messages coming from muted players for everyone else (shown by red lines in the screenshot below):

Please help me to improve my SQL function and note that I do not want to switch to PL/pgSQL for performance reasons.

D-Shih · Answer 1 · 2022-05-03T15:37:37.133

1

I understand correctly, you can try to use EXISTS subquery to judgment mute user in words_get_chat function by your logic.

if nice user we need to judgment which message they want to see.
if bad user we can show all of message.

The function might look like as below.

CREATE OR REPLACE FUNCTION words_get_chat(
        in_gid    integer,
        in_social integer,
        in_sid    text
) RETURNS TABLE (
        out_msg   text
) AS
$func$
-- TODO display messages by muted users only to themselves
SELECT
        c.msg
FROM    words_chat c 
JOIN    words_games g USING (gid) 
JOIN    words_social s ON s.uid IN (g.player1, g.player2)
WHERE   c.gid    = in_gid
AND     s.social = in_social
AND     s.sid    = in_sid
AND     EXISTS (
       SELECT 1
       FROM words_users wu
       WHERE wu.uid = s.uid AND 
       ((muted = true) OR (muted = false AND c.uid = s.uid))
)
ORDER BY c.CREATED ASC;

sqlfiddle

edited May 03 '22 at 15:37

answered May 03 '22 at 15:32

D-Shih

44,943
6
31
51

Thanks it works as expected, but I am struggling to understand your sub-query... I have around 50 000 users in my game (not every user record is an active player, but still...), would it fetch all of them by the sub-query? How to limit it only to the 2 players in the game? (because I do not see that limitation/restriction). – Alexander Farber May 03 '22 at 15:38
1

@AlexanderFarber I think that can be work if the conversation is one on one I try to add more sample data for that https://dbfiddle.uk/?rdbms=postgres_14&fiddle=524a1fce8abd4f96719538ba73de76d1 – D-Shih May 03 '22 at 15:54
1

This join condition `ON s.uid IN (g.player1, g.player2)` might limit only to the 2 players in the game I think – D-Shih May 03 '22 at 15:55
Hi Daniel, I don't think your solution is correct, because when you try a chat of two not-muted users - then [they will only see their own messages](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=eea148c2bafdd97a2e7132949f8722c7) – Alexander Farber May 04 '22 at 12:33

Alexander Farber · Accepted Answer · 2022-05-05T20:05:32.463

Inspired by Daniel's sub query answer and help at the PostgreSQL mailing list, I have developed a CTE and JOIN solution:

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid    integer,
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_mine  integer,
                out_msg   text
        ) AS
$func$
        WITH myself AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c
        JOIN    myself ON TRUE
        JOIN    words_games g USING (gid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     (c.uid = myself.uid 
        -- otherwise only show messages by not muted opponents
                 OR NOT opponent.muted)
        ORDER BY c.created ASC;

$func$ LANGUAGE sql;

Also I received a nice recommendation to separate auth logic from my game's logic:

CREATE OR REPLACE FUNCTION words_get_uid(
                in_social integer,
                in_sid    text
        ) RETURNS integer AS
$func$
        SELECT uid 
        FROM words_social
        WHERE social = in_social
        AND sid = in_sid;
$func$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid   integer,
                in_uid   integer
        ) RETURNS TABLE (
                out_mine integer,
                out_msg  text
        ) AS
$func$
        SELECT
                CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c
        JOIN    words_games g USING (gid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     (c.uid = in_uid 
        -- otherwise only show messages by not muted opponents
                 OR NOT opponent.muted)
        ORDER BY c.created ASC;

$func$ LANGUAGE sql;

SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user;

SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user;

Displaying chat messages of punished users only to themselves

2 Answers2