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/participant.sql | 54 +++++++++++++++++++++++++++----------------------
 1 file changed, 30 insertions(+), 24 deletions(-)

(limited to 'queries/participant.sql')

diff --git a/queries/participant.sql b/queries/participant.sql
index d1d81ce..e11179c 100644
--- a/queries/participant.sql
+++ b/queries/participant.sql
@@ -1,28 +1,34 @@
+WITH rosters AS (SELECT
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1),
+participants AS (SELECT
+  JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
+FROM rosters)
 SELECT
 
-id AS "apiId",
-relationships->'player'->'data'->>'id' AS "playerId",
+participant->'data'->>'id' AS "apiId",
+participant->'relations'->0->'data'->>'id' AS "player_apiId",
 
-attributes->>'actor' AS "hero",
-(attributes->'stats'->>'assists')::int AS "assists",
-COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
-(attributes->'stats'->>'deaths')::int AS "deaths",
-(attributes->'stats'->>'farm')::float AS "farm",
-(attributes->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
-COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
-COALESCE(NULLIF(attributes->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
-COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
-COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'level', ''), '0')::int AS "level",
-COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
-COALESCE(NULLIF(attributes->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
-COALESCE(NULLIF(attributes->'stats'->>'skinKey', ''), '0') AS "skinKey",
-(attributes->'stats'->>'wentAfk')::bool::bool AS "wentAfk",
-(attributes->'stats'->>'winner')::bool::bool AS "winner",
-attributes->'stats'->'itemGrants' AS "itemGrants",
-attributes->'stats'->'itemSells' AS "itemSells",
-attributes->'stats'->'itemUses' AS "itemUses",
-attributes->'stats'->'items' AS "items"
+participant->'data'->'attributes'->>'actor' AS "hero",
+(participant->'data'->'attributes'->'stats'->>'assists')::int AS "assists",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
+(participant->'data'->'attributes'->'stats'->>'deaths')::int AS "deaths",
+(participant->'data'->'attributes'->'stats'->>'farm')::float AS "farm",
+(participant->'data'->'attributes'->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'kills', ''), '0')::int AS "kills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'level', ''), '0')::int AS "level",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skinKey', ''), '0') AS "skinKey",
+(participant->'data'->'attributes'->'stats'->>'wentAfk')::bool AS "wentAfk",
+(participant->'data'->'attributes'->'stats'->>'winner')::bool AS "winner",
+participant->'data'->'attributes'->'stats'->'itemGrants' AS "itemGrants",
+participant->'data'->'attributes'->'stats'->'itemSells' AS "itemSells",
+participant->'data'->'attributes'->'stats'->'itemUses' AS "itemUses",
+participant->'data'->'attributes'->'stats'->'items' AS "items"
 
-FROM "participant"
+FROM participants
-- 
cgit v1.3.1

