Re: SELECT but only if not present in another table
Thank you, Steve - On Sun, Dec 6, 2020 at 6:50 PM Steve Baldwin wrote: > Can't you just use table aliases? So, the outer word_moves would become > 'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the > where clause 'WHERE wp.mid = wm.mid' ? > table aliases have worked for me!
Re: SELECT but only if not present in another table
Can't you just use table aliases? So, the outer word_moves would become
'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the
where clause 'WHERE wp.mid = wm.mid' ?
hth,
Steve
On Mon, Dec 7, 2020 at 4:08 AM Alexander Farber
wrote:
> Good evening,
>
> in PostgreSQL 13.1 I save player moves in the table:
>
> # \d words_moves
> Table "public.words_moves"
> Column | Type | Collation | Nullable |
>Default
>
> -+--+---+--+--
> mid | bigint | | not null |
> nextval('words_moves_mid_seq'::regclass)
> action | text | | not null |
> gid | integer | | not null |
> uid | integer | | not null |
> played | timestamp with time zone | | not null |
> tiles | jsonb| | |
> score | integer | | |
> str | text | | |
> hand| text | | |
> letters | character(1)[] | | |
> values | integer[]| | |
> Indexes:
> "words_moves_pkey" PRIMARY KEY, btree (mid)
> "words_moves_gid_played_idx" btree (gid, played DESC)
> "words_moves_puzzle_idx" btree (puzzle)
> "words_moves_uid_action_played_idx" btree (uid, action, played)
> "words_moves_uid_idx" btree (uid)
> Check constraints:
> "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
> ON DELETE CASCADE
> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
> ON DELETE CASCADE
> Referenced by:
> TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>
> Some of the moves can be "interesting" in the sense that the player have
> used all 7 letter tiles or achieved a high score over 90 points,
>
> I want to display those moves as "puzzles" and have prepared a table to
> store, per-user, who has solved them:
>
> # \d words_puzzles
> Table "public.words_puzzles"
> Column | Type | Collation | Nullable | Default
> +--+---+--+-
> mid| bigint | | not null |
> uid| integer | | not null |
> solved | timestamp with time zone | | not null |
> Foreign-key constraints:
> "words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
> ON DELETE CASCADE
> "words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
> ON DELETE CASCADE
>
> Now I am trying to create a custom stored function which would return just
> one mid (move id) which is not too new (1 year old) and the user has not
> tackled it yet:
>
> CREATE OR REPLACE FUNCTION words_daily_puzzle(
> in_uid int
> ) RETURNS table (
> out_mid bigint,
> out_secret text
> ) AS
> $func$
> SELECT
> mid,
> MD5(mid ||'my little secret')
> FROM words_moves
> WHERE action = 'play'
> AND (LENGTH(str) = 7 OR score > 90)
> AND played BETWEEN CURRENT_TIMESTAMP - INTERVAL '51 week' AND
> CURRENT_TIMESTAMP - INTERVAL '50 week'
> -- the user has not solved this puzzle yet
> AND NOT EXISTS (SELECT 1 FROM words_puzzles WHERE mid =
> the_outer_mid AND uid = in_uid)
> ORDER BY PLAYED ASC
> LIMIT 1;
> $func$ LANGUAGE sql;
>
> As you can see I am missing 1 piece - how do I address the outer SELECT
> mid from the EXISTS-SELECT?
>
> I have written "the_outer_mid" there.
>
> Should I use LEFT JOIN LATERAL here? I have difficulties wrapping my head
> around this.
>
> TLDR: how to return 1 mid from 1 year ago, which is not solved by the user
> in_uid yet?
>
> Thank you
> Alex
>
>
>
SELECT but only if not present in another table
Good evening,
in PostgreSQL 13.1 I save player moves in the table:
# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Some of the moves can be "interesting" in the sense that the player have
used all 7 letter tiles or achieved a high score over 90 points,
I want to display those moves as "puzzles" and have prepared a table to
store, per-user, who has solved them:
# \d words_puzzles
Table "public.words_puzzles"
Column | Type | Collation | Nullable | Default
+--+---+--+-
mid| bigint | | not null |
uid| integer | | not null |
solved | timestamp with time zone | | not null |
Foreign-key constraints:
"words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE
Now I am trying to create a custom stored function which would return just
one mid (move id) which is not too new (1 year old) and the user has not
tackled it yet:
CREATE OR REPLACE FUNCTION words_daily_puzzle(
in_uid int
) RETURNS table (
out_mid bigint,
out_secret text
) AS
$func$
SELECT
mid,
MD5(mid ||'my little secret')
FROM words_moves
WHERE action = 'play'
AND (LENGTH(str) = 7 OR score > 90)
AND played BETWEEN CURRENT_TIMESTAMP - INTERVAL '51 week' AND
CURRENT_TIMESTAMP - INTERVAL '50 week'
-- the user has not solved this puzzle yet
AND NOT EXISTS (SELECT 1 FROM words_puzzles WHERE mid =
the_outer_mid AND uid = in_uid)
ORDER BY PLAYED ASC
LIMIT 1;
$func$ LANGUAGE sql;
As you can see I am missing 1 piece - how do I address the outer SELECT mid
from the EXISTS-SELECT?
I have written "the_outer_mid" there.
Should I use LEFT JOIN LATERAL here? I have difficulties wrapping my head
around this.
TLDR: how to return 1 mid from 1 year ago, which is not solved by the user
in_uid yet?
Thank you
Alex
