From 4c05d4522bb559394f99147cae56ab34b077f0d8 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Sun, 26 Feb 2017 17:21:00 +0100
Subject: rewrite to use joblib (fixes #20), use updated raw schema, check for
 duplicates

---
 queries/roster.sql | 31 ++++++++++++++++---------------
 1 file changed, 16 insertions(+), 15 deletions(-)

(limited to 'queries/roster.sql')

diff --git a/queries/roster.sql b/queries/roster.sql
index eaf0ed6..c61967b 100644
--- a/queries/roster.sql
+++ b/queries/roster.sql
@@ -1,17 +1,18 @@
+WITH rosters AS (SELECT
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1)
 SELECT
+roster->'data'->>'id' as "apiId",
+(roster->'data'->'attributes'->'stats'->>'acesEarned')::int AS "acesEarned",
+(roster->'data'->'attributes'->'stats'->>'gold')::int AS "gold",
+(roster->'data'->'attributes'->'stats'->>'heroKills')::int AS "heroKills",
+(roster->'data'->'attributes'->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
+roster->'data'->'attributes'->'stats'->>'side' AS "side",
+roster->'data'->'attributes'->'stats'->>'side' AS "teamColor",
+(roster->'data'->'attributes'->'stats'->>'turretKills')::int AS "turretKills",
+(roster->'data'->'attributes'->'stats'->>'turretsRemaining')::int AS "turretsRemaining",
 
-id AS "apiId",
-(attributes->'stats'->>'acesEarned')::int AS "acesEarned",
-(attributes->'stats'->>'gold')::int AS "gold",
-(attributes->'stats'->>'heroKills')::int AS "heroKills",
-(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
-attributes->'stats'->>'side' AS "side",
-attributes->'stats'->>'side' AS "teamColor",
-(attributes->'stats'->>'turretKills')::int AS "turretKills",
-(attributes->'stats'->>'turretsRemaining')::int AS "turretsRemaining",
-
-relationships->'participants'->'data'->0->>'id' AS "participant_1",
-relationships->'participants'->'data'->1->>'id' AS "participant_2",
-relationships->'participants'->'data'->2->>'id' AS "participant_3"
-
-FROM "roster"
+roster->'relations'->0->'data'->>'id' AS "participant_1",
+roster->'relations'->1->'data'->>'id' AS "participant_2",
+roster->'relations'->2->'data'->>'id' AS "participant_3"
+FROM rosters
-- 
cgit v1.3.1

