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

On Wed, May 4, 2022 at 7:40 AM Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

>
> 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:
>
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=48d4bef569d966021e94c72f86d9fce5
>
> CREATE OR REPLACE FUNCTION words_get_chat(
> in_gid integer,
> in_social integer,
> in_sid text
> )
>

I suppose it depends on how you call this function - I would personally
separate external authentication and identity from internal business
logic. i.e., look up the uid given the social information in one place and
then write queries like this one against u_id. AFAICS, the social table
provides no benefit to this query that cannot be gotten via uid. It serves
to map social info to uid. If you must keep that logic here I strongly
suggest you place it into a CTE to call out its purpose in mapping social
to user for purposes of figuring out who "me" is. "them" is just going to
be a join against user since you won't have any relevant social information
for them anyway.

> 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))
>

That wasn't my suggestion - you still don't know whether u1 is "me" or
"them", you've just put player1 into the u1 slot.

> ...but how to bring the u1.muted or u2.muted there?
>
>
You can always write something like: CASE WHEN ... THEN u1.muted ELSE
u2.muted END if you don't want to pre-define "me" and "them"

David J.