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.
