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

On Wed, May 4, 2022 at 5:48 AM Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

>
> 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 u ON (u.uid IN (g.player1, g.player2)
> -- The condition below is broken if both users are not
> muted
> AND (u.muted OR (c.uid = u.uid AND NOT u.muted)))
> JOIN words_social s ON (s.uid = u.uid)
> WHERE c.gid = in_gid
> AND s.social = in_social
> AND s.sid = in_sid
> ORDER BY c.CREATED ASC;
>
> $func$ LANGUAGE sql;
>
> For a chat of a bad and a nice user it seemingly works:
>
> SELECT words_get_chat(10, 100, 'abc') AS nice_user;
> SELECT words_get_chat(10, 200, 'def') AS muted_user;
>
> But if you change both users to be not muted - it will break and they only
> will see their own messages.
>

Optimize for performance second. I would move the test regarding muted to
a where clause

I'm not understanding how a given user can see anything but their own
messages where you have the condition s.social = in_social.

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)

Me: u.uid in (player...) and (s.uid = u.uid)
Them: u.uid in (player...) and (s.uid <> u.uid)

Hopefully you get the idea, your "social" dynamic makes this more
challenging. If you can just pass "my uid" into the function then figuring
out which uid is "me" and which is "not me" becomes quite a bit easier.

David J.