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 <alexander.far...@gmail.com>
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
>
>
>
