Thank you, that was it!
On Thursday, May 5, 2022, Alexander Farber
wrote:
> Good evening, I still have a problem with my JOIN expression -
> when I add more games, then messages from other games are displayed:
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=
> e2ff211f59090d1eeab879498148f907
>
> CREATE OR REPLACE FUN
Good evening, I still have a problem with my JOIN expression -
when I add more games, then messages from other games are displayed:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e2ff211f59090d1eeab879498148f907
CREATE OR REPLACE FUNCTION words_get_chat(
in_gid integer,
Good morning, this is a very insightful comment (among many) by you, David -
On Wed, May 4, 2022 at 5:40 PM David G. Johnston
wrote:
> Well, that is basically why I was going on about the oddity of having
> social be a part of the main query. Personally I would write it as
> "myself.uid = in_ui
David, thanks but what do you mean by the last comment -
On Wed, May 4, 2022 at 7:44 PM David G. Johnston
wrote:
> 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 mys
On Wed, May 4, 2022 at 10:23 AM Alexander Farber
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_soci
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
)
SELE
I think I am very close with the following CTE, but do not understand how
to bring it into the main SELECT query:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ee264dc98b44dee75aa4523164a327b3
WITH myself AS (
SELECT uid
FROM words_social
WHERE social =
On Wed, May 4, 2022 at 9:12 AM Alexander Farber
wrote:
> I have tried CROSS JOIN and CASE WHEN (why be greedy, right?):
>
>
> https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43a33374d15a9330145007702138822c
>
> WITH myself AS (
> SELECT uid
> FROM words_social
>
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?):
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43a33374d15a9330145007702138822c
WITH myself AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
On Wed, May 4, 2022 at 8:53 AM Alexander Farber
wrote:
>
> JOINcte
> WHERE c.gid= in_gid
> AND (c.uid = myself.uid OR NOT opponent.muted)
> ORDER BY c.CREATED ASC;
>
> ERROR: syntax error at or near "WHERE"
> LINE 67: WHERE c.gid= in_gi
I try with a CTE but cannot figure the syntax:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=acd6d06a7ea2efc73a0771530832d77e
WITH cte AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
LIMIT 1
)
On Wed, May 4, 2022 at 8:36 AM Alexander Farber
wrote:
> David, I try then the following -
>
> On Wed, May 4, 2022 at 5:28 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> You missed quoting the part where I describe the on clauses you need to
>> distinguish between "them" and "me
I am probably needing LEFT JOIN LATERAL here (and am completely lost)?
Or to switch to CTE as you suggest
David, I try then the following -
On Wed, May 4, 2022 at 5:28 PM David G. Johnston
wrote:
> 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.
On Wed, May 4, 2022 at 8:21 AM Alexander Farber
wrote:
> David, I am trying your suggestion:
>
> On Wed, May 4, 2022 at 4:27 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Assuming the base query is capable of returning all related chat messages
>> for both users (I'd probably p
David, I am trying your suggestion:
On Wed, May 4, 2022 at 4:27 PM David G. Johnston
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
On Wed, May 4, 2022 at 7:40 AM Alexander Farber
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 netw
My real SQL function has one more param, an "auth" string generated by my
game, which complements the social network id "sid".
I have just omitted it in my test case.
>
Hi Ron,
On Wed, May 4, 2022 at 4:56 PM Ron wrote:
>
> How do other web sites know to present only "my" data, even though they
> don't encode "my" user id in the URL?
>
>
that is the usual pattern with OAuth provided by: Facebook, Google, Amazon,
Huawei, etc...
After you auth with them in a game
On 5/4/22 09:40, Alexander Farber wrote:
Thank you for replying, David!
The "social dynamic" is needed, because I cannot pass real user id (via
HTTP) to SQL queries.
How do other web sites know to present only "my" data, even though they
don't encode "my" user id in the URL?
--
Angular mom
Thank you for replying, David!
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 b
On Wed, May 4, 2022 at 5:48 AM Alexander Farber
wrote:
>
> CREATE OR REPLACE FUNCTION words_get_chat(
> in_gidinteger,
> in_social integer,
> in_sidtext
> ) RETURNS TABLE (
> out_mine integer,
> out_m
23 matches
Mail list logo
