Should I use LATERAL JOIN? On Fri, Oct 21, 2016 at 3:24 PM, Alexander Farber < alexander.far...@gmail.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. > >
