Re: Forward declaration of table

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 3:33 PM
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Forward declaration of table

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <ineyman(at)perceptron(dot)com<mailto:ineyman(at)perceptron(dot)com>> wrote:
mailto:pgsql-general-owner(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org>] On Behalf Of Alexander Farber
https://gist.github.com/afarber/c40b9fc5447335db7d24

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE

you are correct, but I need to send most recent move in each game together with the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve them every time dynamically with
WITH last_moves AS (
SELECT *
FROM words_moves wm1
WHERE
played = (SELECT max(played)
FROM words_moves wm2
WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
player1 = 1 OR
player2 = 1;

Regards
Alex

Or, for the last moves you could probably have the third table LAST_MOVES maintained through triggers on WORDS_MOVES table.
Then, you just join WORDS_GAMES and LAST_MOVES tables.

Regards,
Igor