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
