api: demo /matches endpoint

1 files changed, 44 insertions, 0 deletions

diff --git a/queries.py b/queries.py
new file mode 100644
index 0000000..52d03ad
--- /dev/null
+++ b/queries.py
@@ -0,0 +1,44 @@
+#!/usr/bin/python
+
+# TODO: read this from sql files
+# TODO: index
+
+create_index = """
+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'));
+"""
+
+matches = """
+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
+"""