Re: Displaying chat by punished users only to themselves (db fiddle attached)

[Alexander Farber]

David, I am trying your suggestion:

On Wed, May 4, 2022 at 4:27 PM David G. Johnston <david.g.johns...@gmail.com>
wrote:

> Assuming the base query is capable of returning all related chat messages
> for both users (I'd probably place that portion into a CTE) the rows you
> want to filter out are those whose c.uid is not my own, but only if their
> muted property is true.  It makes it easier to understand if you join
> words_users twice, defining one as "them" and one as "me".  Then you can
> say something like:  WHERE (c.uid = me.uid) OR NOT(them.muted)
>
>
like this:


https://dbfiddle.uk/?rdbms=postgres_14&fiddle=4ab6a09cddae26a11140202fdc41cf5c

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$
        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_users myself ON (myself.uid IN (g.player1,
g.player2))
        JOIN    words_users opponent ON (opponent.uid IN (g.player1,
g.player2))
        JOIN    words_social s ON (s.uid = myself.uid)
        WHERE   c.gid    = in_gid
        AND     s.social = in_social
        AND     s.sid    = in_sid
        AND     (c.uid = myself.uid OR NOT opponent.muted)

        ORDER BY c.CREATED ASC;

$func$ LANGUAGE sql;