From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber Sent: Tuesday, August 23, 2016 3:33 PM Cc: pgsql-general <pgsql-general@postgresql.org> Subject: Re: [GENERAL] Forward declaration of table
Hi Igor, On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <iney...@perceptron.com<mailto:iney...@perceptron.com>> wrote: mailto:pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.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
