Re: Forward declaration of table
| От | Igor Neyman |
|---|---|
| Тема | Re: Forward declaration of table |
| Дата | |
| Msg-id | MWHPR07MB2877A4811B306B5DE275F791DAEB0@MWHPR07MB2877.namprd07.prod.outlook.com обсуждение исходный текст |
| Ответ на | Re: Forward declaration of table (Alexander Farber <alexander.farber@gmail.com>) |
| Ответы |
Re: Forward declaration of table
|
| Список | pgsql-general |
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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 <ineyman@perceptron.com> wrote:
mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
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
В списке pgsql-general по дате отправления: