On Thursday, May 5, 2022, Alexander Farber <alexander.far...@gmail.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.

Reply via email to