我为我的问题准备了a simple test case英镑.

在基于PostgreSQL 14.2的an online word game for 2 players中,当有人行为不端时,我将他们的"静音"列设置为"真".

然后,来自被惩罚用户的聊天信息应该对其他人隐藏.

除了受惩罚的用户自己???? - 他们应该看到所有的聊天信息,这样他们就不会注意到自己被静音了,也不会创建新的游戏帐号.

我已经为他们的头像做了这个把戏????

所以我准备了一个简单的测试用例,下面是我的4个表:

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

CREATE TABLE words_social (
    -- social network id
    sid     text     NOT NULL CHECK (sid ~ '\S'),
    -- social network type: 100 = Facebook, 200 = Google, etc.
    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
);

然后我用测试数据填充表格:

-- 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 number 10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);

-- 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??');

最后是我正在努力改进的SQL函数:

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$
        -- TODO display messages by muted users only to themselves
        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_social s ON s.uid IN (g.player1, g.player2)
        WHERE   c.gid    = in_gid
        AND     s.social = in_social
        AND     s.sid    = in_sid
        ORDER BY c.CREATED ASC;

$func$ LANGUAGE sql;

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

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

目前存储的功能显示所有聊天信息,但我想为其他所有人隐藏来自静音玩家的信息(在下面的屏幕截图中用红线显示):

screenshot

请帮助我改进我的SQL函数,并注意,出于性能原因,我不想切换到PL/pgSQL.

推荐答案

受Daniel在PostgreSQL邮件列表中的子查询答案和帮助的启发,我开发了a CTE and JOIN solution个:

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$
        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;

$func$ LANGUAGE sql;

我还从我的游戏逻辑中得到了一个很好的建议to separate auth logic:

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;

Sql相关问答推荐

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数?

如何在postgres函数中插入后返回布尔值?

Access中执行INSERT INTO查询时出现错误消息

更新其组的日期字段值小于最大日期减go 天数的记录

从包含PostgreSQL中的JSON值的列中提取列表或目录

如何在Postgres中为单值输入多行?

如何在AWS Athena中 Select JSON数组的最后一个元素?

用VB.NET在Dapper中实现MS Access数据库顺序透视

snowflake中的动态文件名生成

创建定时器以更新Gridview

使用 PL/PGSQL 函数 Select 返回多条记录

连续日期的SQL

创建一个将层次 struct 级别放入列中的查询

BigQuery 错误:SELECT 列表表达式引用 esthetician.LICENSE_TYPE,它既未在 [49:8] 分组也未聚合

如何在 SQL Server 中参数化 Select top 'n'

为什么这是 AND,OR with NULL 的真值表?

使用同一表中的数据或任何其他虚拟数据在 SQL 表中插入数据的最快方法

如何过滤json嵌套键的值

动态 SQL 存储过程不填充临时表

如何将我的查询包含在我的查询结果中?