Re: Selecting records with highest timestamp - for a join

Adrian, for both player1 and player2 (because I need to display player
photos above the game board).

SQL join with words_social - yes, but how to take the most recent record
from that table?

For example there are user infos from Google+, Facebook, Twitter - but the
user has used Facebook to login lately and would expect her Facebook-photo
to be seen (the record with the highest "stamp" value).

Regards
Alex

On Wed, Oct 19, 2016 at 8:51 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/19/2016 11:35 AM, Alexander Farber wrote:
>
>> In a table I store user info coming from social networks:
>>
>

> 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.
>
>