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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Displaying chat by punished users only to themselves (db fiddle attached)
Дата
Msg-id CAKFQuwb7eFbS69BA+hkcA=Bz=MZcAQjhRTeZK899V=44+ymqpw@mail.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>)
Список pgsql-general
Дерево обсуждения
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.

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

Предыдущее
От: Alexander Farber
Дата:
Сообщение: 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)