From 16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Fri, 27 Jan 2017 13:12:35 +0100
Subject: api: move db queries to sql files

---
 queries/index.sql          |  4 ++++
 queries/recent-matches.sql | 30 ++++++++++++++++++++++++++++++
 2 files changed, 34 insertions(+)
 create mode 100644 queries/index.sql
 create mode 100644 queries/recent-matches.sql

(limited to 'queries')

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
-- 
cgit v1.3.1