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.
