Search Postgresql Archives

SELECT but only if not present in another table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 = "">
        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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux