Thread: Selecting records with highest timestamp - for a join

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.

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.

In PostgreSQL 9.5.3 I keep player infos from various social networks:

# TABLE words_social;
  sid  | social | female |  given  | family | photo | place |   stamp    | uid
-------+--------+--------+---------+--------+-------+-------+------------+-----
 aaaaa |      1 |      0 | Abcde1  |        |       |       | 1470237061 |   1
 aaaaa |      2 |      0 | Abcde2  |        |       |       | 1477053188 |   1
 aaaaa |      3 |      0 | Abcde3  |        |       |       | 1477053330 |   1
 kkkkk |      3 |      0 | Klmnop3 |        |       |       | 1477053810 |   2
 kkkkk |      4 |      0 | Klmnop4 |        |       |       | 1477053857 |   2
 ggggg |      2 |      0 | Ghijk2  |        |       |       | 1477053456 |   3
 ggggg |      3 |      0 | Ghijk3  |        |       |       | 1477053645 |   3
 ggggg |      4 |      0 | Ghijk4  |        |       |       | 1477053670 |   3
 xxxxx |      4 |      0 | Xyzok   |        |       |       | 1470237393 |   4
(9 rows)

The 1,2,3,4 in column "social" means "Facebook", "Twitter", etc.

For a player I can always select her "most recent" info by:

# select * from words_social s1 WHERE stamp = (SELECT max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid);
  sid  | social | female |  given  | family | photo | place |   stamp    | uid
-------+--------+--------+---------+--------+-------+-------+------------+-----
 aaaaa |      3 |      0 | Abcde3  |        |       |       | 1477053330 |   1
 kkkkk |      4 |      0 | Klmnop4 |        |       |       | 1477053857 |   2
 ggggg |      4 |      0 | Ghijk4  |        |       |       | 1477053670 |   3
 xxxxx |      4 |      0 | Xyzok   |        |       |       | 1470237393 |   4
(4 rows)

Then there is another table storing current games (I have omitted some columns with game data below):

# select gid, created, finished, player1, player2 from words_games;
 gid |            created            | finished | player1 | player2
-----+-------------------------------+----------+---------+---------
   1 | 2016-10-21 14:51:12.624507+02 |          |       4 |       1
   2 | 2016-10-21 14:51:22.631507+02 |          |       3 |
(2 rows)

Whenever a user (for example with uid=1) connects to the server, I send her the games she is taking part in:

# select gid, created, finished, player1, player2 from words_games where player1=1
   union select gid, created, finished, player2, player1 from words_games where player2=1;
 gid |            created            | finished | player1 | player2
-----+-------------------------------+----------+---------+---------
   1 | 2016-10-21 14:51:12.624507+02 |          |       4 |       1
(1 row)

My problem: to the above UNION SELECT statement I need to add user infos from words_social table.

(So that I can display user photos and names above the game board)

So I try this with CTE:

# with user_infos AS (select * from words_social s1 WHERE stamp = (SELECT max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid))
 select          g.gid, g.created, g.finished, g.player1, g.player2, i.given from words_games g join user_infos i on (g.player1=i.uid) where g.player1=1
 union select g.gid, g.created, g.finished, g.player2, g.player1, i.given from words_games g join user_infos i on (g.player2=i.uid) where g.player2=1;
 gid |            created            | finished | player1 | player2 | given
-----+-------------------------------+----------+---------+---------+--------
   1 | 2016-10-21 14:51:12.624507+02 |          |       1 |       4 | Abcde3
(1 row)

This works well (I have advanced since me first asking few days ago), but I still have the following problem -

I am worried that the CTE-table user_infos will get very large, once my game has many players.