Trying to fetch records only if preceded by at least another one

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Trying to fetch records only if preceded by at least another one
Date: 2019-10-25 15:12:27
Message-ID: CAADeyWg1RQcfzYzvAKC9kM=YKmNZeHq0vmy5OYvbyckembJY4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yessica Brinkmann 2019-10-25 15:24:08 Re: I think that my data is saved correctly, but when printing again, other data appears
Previous Message Tom Lane 2019-10-25 13:59:37 Re: I think that my data is saved correctly, but when printing again, other data appears