Home > Blockchain >  Displaying chat messages of punished users only to themselves
Displaying chat messages of punished users only to themselves

Time:05-05

I have prepared screenshot

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

CodePudding user response:

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

CodePudding user response:

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
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    myself
        JOIN    words_chat c 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;
  • Related