Good afternoon,
what would be please the best way to generate a list of JSON objects out of an SQL join?But then I realized that with PostgreSQL that part could be spared and after reading https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql I have rewritten my custom function:
CREATE OR REPLACE FUNCTION words_get_longest2(
in_uid integer
) RETURNS json AS
$func$
SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(x))) FROM (
SELECT
ROW_NUMBER() OVER () AS row,
s.gid AS gid,
TO_CHAR(g.created, 'DD.MM.YYYY HH24:MI') AS created,
TO_CHAR(g.finished, 'DD.MM.YYYY HH24:MI') AS finished,
CASE WHEN g.player1 = in_uid THEN g.player1 ELSE g.player2 END AS player1,
CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END AS player2,
CASE WHEN g.player1 = in_uid THEN g.score1 ELSE g.score2 END AS score1,
CASE WHEN g.player1 = in_uid THEN g.score2 ELSE g.score1 END AS score2,
s1.female AS female1,
s2.female AS female2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
s1.place AS place1,
s2.place AS place2,
s.word AS word,
s.score AS score,
m.tiles AS tiles
FROM words_scores s
LEFT JOIN words_games g USING(gid)
LEFT JOIN words_moves m USING(mid)
LEFT JOIN words_social s1 ON s1.uid = in_uid
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s1.uid = s.uid
AND s.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END)
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s2.uid = s.uid
AND s.stamp > s2.stamp)
WHERE s.uid = in_uid
ORDER BY LENGTH(s.word) DESC, s.mid DESC
LIMIT 10
) x;
$func$ LANGUAGE sql STABLE;
words=> select words_get_longest2(2);
words_get_longest2
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
[{"row":2,"gid":1,"created":"17.03.2017 09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"
photo2":"https://vk.com/images/camera_200.png","place1":null,"place2":null,"word":"СМЯТИЕ","score":16,"tiles":[{"col": 5, "row": 13, "value": 1, "letter": "Е"}, {"col": 5, "row": 12, "val
ue": 1, "letter": "И"}, {"col": 5, "row": 11, "value": 2, "letter": "Т"}, {"col": 5, "row": 10, "value": 0, "letter": "Я"}, {"col": 5, "row": 8, "value": 2, "letter": "С"}]},{"row":1,"gid
":1,"created":"17.03.2017 09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"photo2":"https:
//vk.com/images/camera_200.png","place1":null,"place2":null,"word":"МЕХ","score":8,"tiles":[{"col": 6, "row": 9, "value": 1, "letter": "Е"}, {"col": 5, "row": 9, "value": 2, "letter": "М"
}]}]
(1 row)
