Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Alexander Farber
Date:
Hello,

I have developed a complete SQL fiddle for my question:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dcf063ba1615b392cc3cfa347a32c97b

The context is that I run an online game for two players using a PostgreSQL 14.2 backend.

I would like to make my game more friendly by hiding chat messages of misbehaving users.

However, to prevent the punished users from noticing it and registering new game accounts, I would like to still show them all messages :->

So here are the 4 tables used in my reduced test case:

CREATE TABLE words_users (
    uid SERIAL PRIMARY KEY,
    muted BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE words_social (
    sid     text     NOT NULL CHECK (sid ~ '\S'),
    social  integer  NOT NULL CHECK (0 < social AND social <= 256),
    given   text     NOT NULL CHECK (given ~ '\S'),
    uid     integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
    PRIMARY KEY(sid, social)
);

CREATE TABLE words_games (
    gid      SERIAL PRIMARY KEY,
    player1  integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
    player2  integer REFERENCES words_users(uid) ON DELETE CASCADE
);

CREATE TABLE words_chat (
        cid     BIGSERIAL PRIMARY KEY,
        created timestamptz NOT NULL,
        gid     integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
        uid     integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
        msg     text    NOT NULL
);

Then I put 2 users into the game #10 and they start chatting:

-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100, 'Nice user', 1), ('def', 200, 'Bad user', 2);

-- put these 2 users into a game #10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);

-- both users in the game #10 start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');

Here is my custom stored function (in SQL, I would prefer not to switch to PL/pgSQL):

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.

I have tinkered a lot with my db fiddle... but still cannot figure it out

Thank you!
Alex

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

From
"David G. Johnston"
Date:
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.

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

From
Alexander Farber
Date:
Thank you for replying, David! 

The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries.

Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats by other users, by just replacing the numeric "uid"...

So I try your suggestion with:


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 u1 ON (u1.uid = g.player1) 
        JOIN    words_users u2 ON (u2.uid = g.player2) 
        JOIN    words_social s ON (s.uid IN (u1.uid, u2.uid))
        WHERE   c.gid    = in_gid
        AND     s.social = in_social
        AND     s.sid    = in_sid
        ORDER BY c.CREATED ASC;

$func$ LANGUAGE sql;

...but how to bring the u1.muted or u2.muted there?

Best regards
Alex

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

From
Ron
Date:
On 5/4/22 09:40, Alexander Farber wrote:
> Thank you for replying, David!
>
> The "social dynamic" is needed, because I cannot pass real user id (via 
> HTTP) to SQL queries.

How do other web sites know to present only "my" data, even though they 
don't encode "my" user id in the URL?

-- 
Angular momentum makes the world go 'round.

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

From
Alexander Farber
Date:
Hi Ron,

On Wed, May 4, 2022 at 4:56 PM Ron <ronljohnsonjr@gmail.com> wrote:

How do other web sites know to present only "my" data, even though they
don't encode "my" user id in the URL?


that is the usual pattern with OAuth provided by: Facebook, Google, Amazon, Huawei, etc...

After you auth with them in a game like mine, they give you a social network id, which is a string. Noone else gets that str.

And then I (as game dev) use that str to id the user and when the user is visiting my for the 1st time, I give him a numeric id in my game. And an "auth" str generated by my game. Etc... it works ok.

Regards
Alex

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

From
Alexander Farber
Date:
My real SQL function has one more param, an "auth" string generated by my game, which complements the social network id "sid".

I have just omitted it in my test case.

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 7:40 AM Alexander Farber <alexander.farber@gmail.com> wrote:

The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries.

Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats by other users, by just replacing the numeric "uid"...

So I try your suggestion with:


CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid    integer,
                in_social integer,
                in_sid    text
        )

I suppose it depends on how you call this function - I would personally separate external authentication and identity from internal business logic.  i.e., look up the uid given the social information in one place and then write queries like this one against u_id.  AFAICS, the social table provides no benefit to this query that cannot be gotten via uid.  It serves to map social info to uid.  If you must keep that logic here I strongly suggest you place it into a CTE to call out its purpose in mapping social to user for purposes of figuring out who "me" is. "them" is just going to be a join against user since you won't have any relevant social information for them anyway.
 
        JOIN    words_users u1 ON (u1.uid = g.player1) 
        JOIN    words_users u2 ON (u2.uid = g.player2) 
        JOIN    words_social s ON (s.uid IN (u1.uid, u2.uid))

That wasn't my suggestion - you still don't know whether u1 is "me" or "them", you've just put player1 into the u1 slot.
...but how to bring the u1.muted or u2.muted there?


You can always write something like: CASE WHEN ... THEN u1.muted ELSE u2.muted END if you don't want to pre-define "me" and "them"

David J.

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

From
Alexander Farber
Date:
David, I am trying your suggestion:

On Wed, May 4, 2022 at 4:27 PM David G. Johnston <david.g.johnston@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;

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 8:21 AM Alexander Farber <alexander.farber@gmail.com> wrote:
David, I am trying your suggestion:

On Wed, May 4, 2022 at 4:27 PM David G. Johnston <david.g.johnston@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)



You missed quoting the part where I describe the on clauses you need to distinguish between "them" and "me"

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

In particular, the IN expression causes two rows to be returned, one for them and one for me - but for each join you only want one or the other.

David J.

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

From
Alexander Farber
Date:
David, I try then the following -

On Wed, May 4, 2022 at 5:28 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
You missed quoting the part where I describe the on clauses you need to distinguish between "them" and "me"

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



        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) AND myself.uid = s.uid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> s.uid) 
        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;

And get the syntax error which don't quite understand:

ERROR:  missing FROM-clause entry for table "s"
LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) 
                                                                 ^

Probably because "myself" needs "s" and vice versa?


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

From
Alexander Farber
Date:
I am probably needing LEFT JOIN LATERAL here (and am completely lost)?

Or to switch to CTE as you suggest

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 8:36 AM Alexander Farber <alexander.farber@gmail.com> wrote:
David, I try then the following -

On Wed, May 4, 2022 at 5:28 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
You missed quoting the part where I describe the on clauses you need to distinguish between "them" and "me"

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



        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) AND myself.uid = s.uid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> s.uid) 
        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;

And get the syntax error which don't quite understand:

ERROR:  missing FROM-clause entry for table "s"
LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) 
                                                                 ^

Probably because "myself" needs "s" and vice versa?


Well, that is basically why I was going on about the oddity of having social be a part of the main query.  Personally I would write it as "myself.uid = in_uid", but you don't have an in_uid to reference.  Decide how you want to do something equivalent.

David J.

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

From
Alexander Farber
Date:
I try with a CTE but cannot figure the syntax:


        WITH cte AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = cte.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) AND myself.uid = cte.uid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> cte.uid) 
        JOIN    cte
        WHERE   c.gid    = in_gid
        AND     (c.uid = myself.uid OR NOT opponent.muted)
        ORDER BY c.CREATED ASC;

ERROR:  syntax error at or near "WHERE"
LINE 67:         WHERE   c.gid    = in_gid
                 ^

And if I remove the "JOIN cte" line, then the error is:

ERROR:  missing FROM-clause entry for table "cte"
LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) 
                                                               ^

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 8:53 AM Alexander Farber <alexander.farber@gmail.com> wrote:

        JOIN    cte
        WHERE   c.gid    = in_gid
        AND     (c.uid = myself.uid OR NOT opponent.muted)
        ORDER BY c.CREATED ASC;

ERROR:  syntax error at or near "WHERE"
LINE 67:         WHERE   c.gid    = in_gid
                 ^

And if I remove the "JOIN cte" line, then the error is:

ERROR:  missing FROM-clause entry for table "cte"
LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) 

Try "CROSS JOIN cte" - that variant doesn't require a join condition.

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

From
Alexander Farber
Date:
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?):

        WITH myself AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        ),
        opponent AS (
             SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE player1 END
             FROM words_games
             WHERE gid = in_gid
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM  myself CROSS JOIN opponent
        WHERE   (c.uid = myself.uid OR NOT opponent.muted)
        ORDER BY c.CREATED ASC;

but the error is:

ERROR:  missing FROM-clause entry for table "myself"
LINE 60:              SELECT CASE WHEN player1 = myself.uid THEN play...
                                                 ^

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 9:12 AM Alexander Farber <alexander.farber@gmail.com> wrote:
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?):

        WITH myself AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        ),
        opponent AS (
             SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE player1 END
             FROM words_games
             WHERE gid = in_gid
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM  myself CROSS JOIN opponent
        WHERE   (c.uid = myself.uid OR NOT opponent.muted)
        ORDER BY c.CREATED ASC;

but the error is:

ERROR:  missing FROM-clause entry for table "myself"
LINE 60:              SELECT CASE WHEN player1 = myself.uid THEN play...
     

What exactly are you trying to do in the "opponent" cte - and why do you think the myself cte is visible to it?

David J.

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

From
Alexander Farber
Date:
I think I am very close with the following CTE, but do not understand how to bring it into the main SELECT query:


        WITH myself AS (
            SELECT uid
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                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 <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     c.uid = myself.uid
        -- otherwise only show messages by not muted opponents
        OR      NOT opponent.muted
        ORDER BY c.CREATED ASC;

The error message is:

ERROR:  missing FROM-clause entry for table "myself"
LINE 64: ...uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid...
                                                              ^

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

From
Alexander Farber
Date:
Is that the right way to do it?


        WITH myself AS (
            SELECT uid
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    myself
        JOIN    words_chat c ON TRUE
        JOIN    words_games g USING (gid)
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     c.uid = myself.uid
        -- otherwise only show messages by not muted opponents
        OR      NOT opponent.muted
        ORDER BY c.created ASC;

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

From
"David G. Johnston"
Date:
On Wed, May 4, 2022 at 10:23 AM Alexander Farber <alexander.farber@gmail.com> wrote:
Is that the right way to do it?


        WITH myself AS (
            SELECT uid
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    myself
        JOIN    words_chat c ON TRUE
        JOIN    words_games g USING (gid)
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     c.uid = myself.uid
        -- otherwise only show messages by not muted opponents
        OR      NOT opponent.muted
        ORDER BY c.created ASC;

Assuming it provides the correct result, yes.

It's a bit odd to see "from myself" - listing words_chat first makes much more sense.

You've defined (social,sid) as a primary key, your LIMIT 1 just makes you look like you don't know or trust that and leaves the reader wondering.

Using (SELECT uid FROM myself) provides the same result without the from/join reference; the usage in the case and the where clause could be rewritten to use opponent.uid so myself.uid only appears once.

David J.

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

From
Alexander Farber
Date:
David, thanks but what do you mean by the last comment -

On Wed, May 4, 2022 at 7:44 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
Using (SELECT uid FROM myself) provides the same result without the from/join reference; the usage in the case and the where clause could be rewritten to use opponent.uid so myself.uid only appears once.


I have applied your first 2 comments in


        WITH myself AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c
        JOIN    myself ON TRUE
        JOIN    words_games g USING (gid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     c.uid = myself.uid 
        -- otherwise only show messages by not muted opponents
        OR      NOT opponent.muted
        ORDER BY c.created ASC;

but where to put the (SELECT uid FROM myself), I do not understand?

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

From
Alexander Farber
Date:
Good morning, this is a very insightful comment (among many) by you, David -

On Wed, May 4, 2022 at 5:40 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
Well, that is basically why I was going on about the oddity of having social be a part of the main query.  Personally I would write it as "myself.uid = in_uid", but you don't have an in_uid to reference.  Decide how you want to do something equivalent.


so I will rewrite the stored functions in my game to be like that, to separate auth from functionality -


CREATE OR REPLACE FUNCTION words_get_uid(
                in_social integer,
                in_sid    text
        ) RETURNS integer AS
$func$
        SELECT uid 
        FROM words_social
        WHERE social = in_social
        AND sid = in_sid;
$func$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid   integer,
                in_uid   integer
        ) RETURNS TABLE (
                out_mine integer,
                out_msg  text
        ) AS
$func$
        SELECT
                CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END,
                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;

SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user;

SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user;

Thanks
Alex

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

From
Alexander Farber
Date:
Good evening, I still have a problem with my JOIN expression -
when I add more games, then messages from other games are displayed:


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

Best regards
Alex


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

From
"David G. Johnston"
Date:
On Thursday, May 5, 2022, Alexander Farber <alexander.farber@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:


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.

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

From
Alexander Farber
Date:
Thank you, that was it!