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.kla...@aklaver.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. > >
