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

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

From

Alexander Farber

Date:

25 October 2019, 18:12:27

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

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

From

Michael Lewis

Date:

25 October 2019, 20:27:45

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

LIMIT 1 inside your lateral should resolve that. Personally, I'd move that condition to EXISTS condition inside WHERE clause instead. Just a style thing as far as I know.

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

From

Alexander Farber

Date:

25 October 2019, 23:19:55

Thank you Michael -

On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis <mlewis@entrata.com> wrote:

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

LIMIT 1 inside your lateral should resolve that. Personally, I'd move that condition to EXISTS condition inside WHERE clause instead. Just a style thing as far as I know.

How would you do that with EXISTS? I tried, but my query failed to compile

Regards

Alex

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

From

Michael Lewis

Date:

26 October 2019, 01:39:12

On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber <alexander.farber@gmail.com> wrote:

Thank you Michael -

On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis <mlewis@entrata.com> wrote:
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

LIMIT 1 inside your lateral should resolve that. Personally, I'd move that condition to EXISTS condition inside WHERE clause instead. Just a style thing as far as I know.

How would you do that with EXISTS? I tried, but my query failed to compile

Ooops. I didn't notice you had selected the previous move ID. In that case, at the end of the lateral you want ORDER BY mid DESC LIMIT 1, or perhaps order by played field and you should be good to go. Particularly if you have an index on (gid, mid) then it should be very fast. If you want to quickly find these "interesting moves" and they are very rare, a partial index would be quite helpful. Highly stylized, but when it is indexing 1-5% of a huge table perhaps, it can be a very handy tool.

CREATE INDEX idx_interesting_moves ON words_moves USING btree( played ) WHERE action='play' AND length(letters) = 7 and length(hand)=7;