From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Forward declaration of table |
Date: | 2016-08-23 19:32:59 |
Message-ID: | CAADeyWiLcBfWHx6r7Jshsxs3C6ewJ3xGiQ8Dd29OxYUVSFSnDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Igor,
On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2016-08-23 19:52:00 | Re: Forward declaration of table |
Previous Message | Igor Neyman | 2016-08-23 18:15:29 | Re: Forward declaration of table |