diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-01-27 13:12:35 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-01-27 13:12:35 +0100 |
| commit | 16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2 (patch) | |
| tree | f1a7441db5ae6aa94d3c92c1fc0a3122eaebf4cd /queries | |
| parent | 0c7abdcf1887285a5dccf0073629521a91cc0276 (diff) | |
| download | apigrabber-16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2.tar.gz apigrabber-16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2.zip | |
api: move db queries to sql files
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/index.sql | 4 | ||||
| -rw-r--r-- | queries/recent-matches.sql | 30 |
2 files changed, 34 insertions, 0 deletions
diff --git a/queries/index.sql b/queries/index.sql new file mode 100644 index 0000000..5af21b5 --- /dev/null +++ b/queries/index.sql @@ -0,0 +1,4 @@ +CREATE INDEX ON match((data->>'id')); +CREATE INDEX ON roster((data->>'id')); +CREATE INDEX ON participant((data->>'id')); +CREATE INDEX ON match((data->'attributes'->>'createdAt')); diff --git a/queries/recent-matches.sql b/queries/recent-matches.sql new file mode 100644 index 0000000..fb237d7 --- /dev/null +++ b/queries/recent-matches.sql @@ -0,0 +1,30 @@ +SELECT ARRAY_TO_JSON(ARRAY( +SELECT + JSONB_BUILD_OBJECT( + 'id', match.data->>'id', + 'date', match.data->'attributes'->>'createdAt', + 'duration', CAST( + match.data->'attributes'->>'duration' + AS INTEGER), + 'teams', ARRAY( + SELECT( + SELECT + JSONB_BUILD_OBJECT( + 'id', roster.data->>'id', + 'side', roster.data->'attributes'->'stats'->>'side', + 'kills', roster.data->'attributes'->'stats'->>'heroKills', + 'players', ARRAY( + SELECT( + SELECT + participant.data->'attributes'->>'actor' + FROM participant WHERE relparticipant->>'id' = participant.data->>'id') + FROM JSONB_ARRAY_ELEMENTS(roster.data->'relationships'->'participants'->'data') relparticipant + ) + ) + FROM roster WHERE relroster->>'id' = roster.data->>'id') + FROM JSONB_ARRAY_ELEMENTS(match.data->'relationships'->'rosters'->'data') relroster + ) +) +FROM match +ORDER BY match.data->'attributes'->>'createdAt' DESC +)) AS data |