Check if there 6 last records of same type without gaps

On 6 September 2016 at 12:32, Alexander Farber <alexander.farber@gmail.com>
wrote:

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;

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

Regards,
Sándor

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

On 6 September 2016 at 14:23, Alexander Farber <alexander.farber@gmail.com>
wrote:

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

Sry! I wasn't clear enough.

Those are two separate solutions. Pick one!

In this case you don't need the group by

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

Regards,
Sándor

No, I am sorry - for struggling with probably basic questions, but without
GROUP BY I get another 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

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

Hello

On 06.09.2016, at 14:35, Alexander Farber <alexander.farber@gmail.com> wrote:

No, I am sorry - for struggling with probably basic questions, but without GROUP BY I get another 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

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.

Charles

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

Also tried the second suggestion:

words=> select count(action='skip') from words_moves where gid=3 group by
played order by played desc limit 6;
count
-------
1
1
1
1
1
1
(6 rows)

On 6 September 2016 at 15:19, Alexander Farber <alexander.farber@gmail.com>
wrote:

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

My bad. I didn't pay attention.

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;

And now I really hope, I didn't miss something important again!

Regards,
Sándor

Sandor, this has worked, thank you -

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

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;

words=> SELECT SUM(skips) from (SELECT CASE WHEN action='skip' THEN 1 ELSE
0 END as skips, played
words(> FROM words_moves
words(> WHERE gid = 3
words(> ORDER BY played DESC
words(> LIMIT 6) as skipscount;
sum
-----
6
(1 row)