Re: Selecting records with highest timestamp - for a join

Should I use LATERAL JOIN?

On Fri, Oct 21, 2016 at 3:24 PM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

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