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

On Thursday, May 5, 2022, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

> Good evening, I still have a problem with my JOIN expression -
> when I add more games, then messages from other games are displayed:
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=
> e2ff211f59090d1eeab879498148f907
>
> CREATE OR REPLACE FUNCTION words_get_chat(
> in_gid integer,
> in_uid integer
> ) RETURNS TABLE (
> out_mine integer,
> out_game text,
> out_msg text
> ) AS
> $func$
> SELECT
> CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END,
> 'game #' || c.gid,
> 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;
>
> I have tried making the JOIN words_users opponent even more restrictive
> with:
>
> JOIN words_users opponent ON (opponent.uid IN (g.player1,
> g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid)
>
> but still messages from the game #20 are displayed, even though I pass
> in_gid = 10
>

You want: gid and (uid or muted); what you have is: (gid and uid) or
muted; based upon operator precedence.

David J.