Re: Selecting records with highest timestamp - for a join

On 10/19/2016 11:35 AM, Alexander Farber wrote:
> Good evening,
>
> I have a question please on which kind of statement to use -
>
> In a table I store user info coming from social networks (Facebook,
> Twitter, ...):
>
> CREATE TABLE words_social (
> sid varchar(255) NOT NULL,
>
> social integer NOT NULL CHECK (0 <= social AND social <= 6),
> female integer NOT NULL CHECK (female = 0 OR female = 1),
> given varchar(255) NOT NULL CHECK (given ~ '\S'),
> family varchar(255),
> photo varchar(255) CHECK (photo ~* '^https?://...'),
> place varchar(255),
> stamp integer NOT NULL, /* HOW TO USE THE LATEST stamp? */
>
> uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
> PRIMARY KEY(sid, social)
> );
>
> I.e. a user can have several records in the above table, but I always
> use the most recent one (the one with the highest "stamp") to display
> that user in my game.
>
> Then I use a custom function to retrieve current games info for a
> particular user:
>
> CREATE OR REPLACE FUNCTION words_get_games(in_uid integer)
> RETURNS TABLE (
> out_gid integer,
> out_created integer,
> out_finished integer,
> out_player1 integer,
> out_player2 integer,
> out_played1 integer,
> out_played2 integer,
> out_score1 integer,
> out_score2 integer,
> out_hand1 text,
> out_hand2 text,
> out_letters varchar[15][15],
> out_values integer[15][15],
> out_bid integer,
> out_last_tiles jsonb,
> out_last_score integer
> ) AS
> $func$
> SELECT
> g.gid,
> EXTRACT(EPOCH FROM g.created)::int,
> EXTRACT(EPOCH FROM g.finished)::int,
> g.player1,
> g.player2, -- can be NULL
> EXTRACT(EPOCH FROM g.played1)::int,
> EXTRACT(EPOCH FROM g.played2)::int,
> g.score1,
> g.score2,
> ARRAY_TO_STRING(g.hand1, ''),
> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g'),
> g.letters,
> g.values,
> g.bid,
> m.tiles,
> m.score
> FROM words_games g LEFT JOIN words_moves m USING(mid)
> WHERE g.player1 = in_uid
> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
> INTERVAL '1 day')
> UNION SELECT
> g.gid,
> EXTRACT(EPOCH FROM g.created)::int,
> EXTRACT(EPOCH FROM g.finished)::int,
> g.player2,
> g.player1, -- can not be NULL
> EXTRACT(EPOCH FROM g.played2)::int,
> EXTRACT(EPOCH FROM g.played1)::int,
> g.score2,
> g.score1,
> ARRAY_TO_STRING(g.hand2, ''),
> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g'),
> g.letters,
> g.values,
> g.bid,
> m.tiles,
> m.score
> FROM words_games g LEFT JOIN words_moves m USING(mid)
> WHERE g.player2 = in_uid
> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
> INTERVAL '1 day');
>
> $func$ LANGUAGE sql;
>
> I would like to extend the above custom function, so that user info
> (given and last names, photo) is returned too.
>
> How to approach this problem please, should I use CTE for this?

For player1, player2 or both?

Since you are returning a table from words_get_games() you can
experiment by joining it's output to words_social.

>
> Thank you for any hints
> Alex
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com