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.
