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)
Дата
Msg-id CAADeyWgt2Ch3iZFmRrwvZFmwubAwO-8ZD-Di5utEQW4XOqWUUw@mail.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)  (Ron <ronljohnsonjr@gmail.com>)
Re: Displaying chat by punished users only to themselves (db fiddle attached)  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
Дерево обсуждения
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Displaying chat by punished users only to themselves (db fiddle attached)
Следующее
От: Ron
Дата:
Сообщение: Re: Displaying chat by punished users only to themselves (db fiddle attached)