Re: Forward declaration of table

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

mailto:pgsql-general-owner@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