On Wed, May 4, 2022 at 10:23 AM Alexander Farber <alexander.far...@gmail.com>
wrote:

> Is that the right way to do it?
>
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7bd74243397da61ddc4c216ad919c7cc
>
>         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.

Reply via email to