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

David, I am trying your suggestion:

On Wed, May 4, 2022 at 4:27 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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;