Re: Displaying chat by punished users only to themselves (db fiddle attached) - Mailing list pgsql-general

From David G. Johnston
Subject Re: Displaying chat by punished users only to themselves (db fiddle attached)
Date
Msg-id CAKFQuwZ9erudCJ9npv5_dZEsuyV6QPv_sH2JZPJZZHZQuxhsfA@mail.gmail.com
Whole thread Raw
In response to Displaying chat by punished users only to themselves (db fiddle attached)  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: Displaying chat by punished users only to themselves (db fiddle attached)
Re: Displaying chat by punished users only to themselves (db fiddle attached)
List pgsql-general
On Wed, May 4, 2022 at 5:48 AM Alexander Farber <alexander.farber@gmail.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.

pgsql-general by date:

Previous
From: Alexander Farber
Date:
Subject: Displaying chat by punished users only to themselves (db fiddle attached)
Next
From: Alexander Farber
Date:
Subject: Re: Displaying chat by punished users only to themselves (db fiddle attached)