Search Postgresql Archives

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

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

 



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

[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