Thread: Check if there 6 last records of same type without gaps

Good afternoon,

for a 2-player game I store moves in the following 9.5.4 table:

CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign');

CREATE TABLE words_moves (
        mid SERIAL PRIMARY KEY,
        action words_action NOT NULL,
        gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
        uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
        played timestamptz NOT NULL,
        tiles jsonb,
        score integer CHECK (score > 0)
);

ALTER TABLE words_games ADD CONSTRAINT words_mid_fk FOREIGN KEY (mid) REFERENCES words_moves;

And then I have a custom function for skipping a move (and inserting a 'skip' into the above table):

CREATE OR REPLACE FUNCTION words_skip_game(
        IN in_uid integer,
        IN in_gid integer,
        OUT out_gid integer)
        RETURNS integer AS
$func$
DECLARE
        _finished timestamptz;
        _score1   integer;
        _score2   integer;
        _uid2     integer;
BEGIN
        INSERT INTO words_moves (
                action,
                gid,
                uid,
                played
        ) VALUES (
                'skip',
                in_gid,
                in_uid,
                CURRENT_TIMESTAMP
        );

Could you please suggest a good way to check that the last 6 moves where 'skip', so that I can end the game when each player skipped her move 3 times in a row?

        IF /* there are 6 'skip's - how to do it please? */ THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

Below is the rest of my function, thank you for any ideas -

Regards
Alex

        UPDATE words_games SET
                finished = _finished,
                played1  = CURRENT_TIMESTAMP
        WHERE
                gid      = in_gid AND
                player1  = in_uid AND
                finished IS NULL AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2)
        RETURNING
                gid,
                score1,
                score2,
                player2
        INTO
                out_gid,
                _score1, -- my score
                _score2, -- her score
                _uid2;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        finished = _finished,
                        played2  = CURRENT_TIMESTAMP
                WHERE
                        gid      = in_gid AND
                        player2  = in_uid AND
                        finished IS NULL AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1);
                RETURNING
                        gid,
                        score2,  -- swapped
                        score1,
                        player1
                INTO
                        out_gid,
                        _score1,  -- my score
                        _score2,  -- her score
                        _uid2;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        -- game over, update win/loss/draw stat for both players
        IF _finished IS NOT NULL THEN
                IF _score1 > _score2 THEN

                        UPDATE words_users SET
                                win = win + 1
                        WHERE uid = in_uid;

                        UPDATE words_users SET
                                loss = loss + 1
                        WHERE uid = _uid2;

                ELSIF _score1 < _score2 THEN

                        UPDATE words_users SET
                                loss = loss + 1
                        WHERE uid = in_uid;

                        UPDATE words_users SET
                                win = win + 1
                        WHERE uid = _uid2;
                ELSE
                        UPDATE words_users SET
                                draw = draw + 1
                        WHERE uid = in_uid OR uid = _uid2;
                END IF;
        END IF;
END
$func$ LANGUAGE plpgsql;

Thank you, Sandor -

On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku.sandor@gmail.com> wrote:

Get the last 6 record and 

1.  ... action='SKIP' as isskip ... then you can group on and count the skip moves. If there is 6 of them the game ends.

2.  ... sum(case when action='SKIP' then 1 else 0 end) ... If the result is 6 the game ends

I am trying

        SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
        FROM words_moves
        GROUP BY action
        ORDER BY played DESC
        LIMIT 6
        INTO _sum;

        RAISE NOTICE '_sum = %', _sum;

        IF _sum = 6 THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

but get the error -

org.postgresql.util.PSQLException: ERROR:
column "words_moves.played" must appear in the GROUP BY clause or be used in an aggregate function| 
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL statement

Regards

Alex

P.S: Here is the table in question

                                  Table "public.words_moves"
 Column |           Type           |                         Modifiers
--------+--------------------------+-----------------------------------------------------------
 mid    | integer                  | not null default nextval('words_moves_mid_seq'::regclass)
 action | words_action             | not null
 gid    | integer                  | not null
 uid    | integer                  | not null
 played | timestamp with time zone | not null
 tiles  | jsonb                    |
 score  | integer                  |
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
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_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid) REFERENCES words_moves(mid)
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

Hello Charles and other, please excuse my stupidity, but -

On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:

You must group by played, as the message suggests. You are implicitly selecting the column through order by, although you don't have it in the list of selected columns.

Here I have 7 "skip" events for gid=3 ("game id") in the table:

words=> select mid, action, gid, uid from words_moves order by played desc;
 mid | action | gid | uid
-----+--------+-----+-----
  15 | skip   |   3 |   1
  14 | skip   |   3 |   2
  13 | skip   |   3 |   1
  12 | skip   |   3 |   2
  11 | skip   |   3 |   1
  10 | skip   |   3 |   2
   9 | skip   |   3 |   1
   6 | play   |   3 |   2
   5 | play   |   4 |   1
   3 | swap   |   3 |   1
   2 | play   |   2 |   1
   1 | play   |   1 |   1
(12 rows)

And then I try the suggestion I got in this mailing list:

words=>         SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
words->         FROM words_moves
words->         WHERE gid = 3
words->         GROUP BY played
words->         ORDER BY played DESC
words->         LIMIT 6;
 sum
-----
   1
   1
   1
   1
   1
   1
(6 rows)

I guess I need ASC in the last statement, but main problem is how to get the total sum...

Regards

Alex

Of course you need the played field you relied on it in the order by clause. You can use the result of a select in a from clause of another select.

SELECT SUM(skips) from (SELECT CASE WHEN action='skip' THEN 1 ELSE 0 END as skips, played

         FROM words_moves
         WHERE gid = 3
         ORDER BY played DESC

         LIMIT 6) as skipscount;