Re: SELECT but only if not present in another table - Mailing list pgsql-general

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