Postgres Pro
Downloads
Home   >   mailing lists

Re: Forward declaration of table - Mailing list pgsql-general

From Alexander Farber
Subject Re: Forward declaration of table
Date
Msg-id CAADeyWiLcBfWHx6r7Jshsxs3C6ewJ3xGiQ8Dd29OxYUVSFSnDg@mail.gmail.com
Whole thread Raw
In response to Re: Forward declaration of table  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: Forward declaration of table  (Igor Neyman <ineyman@perceptron.com>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Igor Neyman
Date:
Subject: Re: Forward declaration of table
Next
From: Igor Neyman
Date:
Subject: Re: Forward declaration of table