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

Поиск
Список
Период
Сортировка
Искать
От
Alexander Farber
Тема
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Дата
в 15:20:53
Msg-id
CAADeyWhZamV5ZEkvwEj-Qnm+NcSsx+0T7cz6nM0hCwOvZbqPOg@mail.gmail.com
Ответ на
Re: Displaying chat by punished users only to themselves (db fiddle attached) (David G. Johnston)
Список
Дерево обсуждения
Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Ron <ronljohnsonjr@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) "David G. Johnston" <david.g.johnston@gmail.com>
Re: Displaying chat by punished users only to themselves (db fiddle attached) Alexander Farber <alexander.farber@gmail.com>
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;
В списке pgsql-general по дате отправления
Предыдущее
От: David G. Johnston
Дата:
Следующее
От: David G. Johnston
Дата:
FAQ