Good evening,
for a word game hosted on PostgreSQL 10 I try to find interesting player
moves (high score or played all 7 tiles) and generate a "puzzle" images out
of them (example: https://imgur.com/a/StnXqoR )
The moves are stored in:
words_ru=> \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 | | |
letters | text | | |
hand | text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"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_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
So I try to find 5 most old interesting moves and it works:
words_ru=> select
m.mid, -- interesting move id
m.gid,
s.word,
m.tiles
from words_moves m
left join words_scores s using (mid)
where m.action='play'
and length(m.letters)=7
and length(m.hand)=7
and length(s.word)=7
order by m.played asc
limit 5;
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid | 77461
gid | 1048
word | СОПЕНИЕ
tiles | [{"col": 7, "row": 1, "value": 2, "letter": "С"}, {"col": 7, "row":
2, "value": 1, "letter": "О"}, {"col": 7, "row": 3, "value": 2, "letter":
"П"}, {"col": 7, "row": 4, "value": 1, "letter": "Е"}, {"col": 7, "row": 5,
"value": 1, "letter": "Н"}, {"col": 7, "row": 6, "value": 1, "letter":
"И"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}]
-[ RECORD 2
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid | 78406
gid | 5702
word | СОПИЛКА
tiles | [{"col": 5, "row": 14, "value": 2, "letter": "С"}, {"col": 6,
"row": 14, "value": 1, "letter": "О"}, {"col": 7, "row": 14, "value": 2,
"letter": "П"}, {"col": 8, "row": 14, "value": 1, "letter": "И"}, {"col":
9, "row": 14, "value": 2, "letter": "Л"}, {"col": 10, "row": 14, "value":
2, "letter": "К"}, {"col": 11, "row": 14, "value": 1, "letter": "А"}]
However there is one problem: I only want to fetch those "interesting"
moves which are preceded by at least another one move in the same game -
because otherwise the game board is empty and the puzzle is boring.
So I have tried to add a JOIN LATERAL:
select
m.mid, -- interesting move id
m2.mid, -- preceding move id in the same game id
m.gid,
s.word,
m.tiles
from words_moves m
left join words_scores s using (mid)
join lateral (SELECT * FROM words_moves WHERE gid=m.gid and mid < m.mid) AS
m2 ON TRUE
where m.action='play'
and length(m.letters)=7
and length(m.hand)=7
and length(s.word)=7
order by m.played asc
limit 5;
But it prints too many records: all moves cross-multiplied with each other.
As if I have forgotten to add 1 more condition to the JOIN LATERAL
Do you please have any ideas here? (I hope my context is not too confusing
:-)
Regards
Alex
