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;