From cfb59a7772aad708120c953fad30e88986f82441 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Thu, 16 Feb 2017 18:11:25 +0100
Subject: workaround for participants not including some data

---
 queries/match_participation.sql | 18 +++++++++---------
 1 file changed, 9 insertions(+), 9 deletions(-)

(limited to 'queries/match_participation.sql')

diff --git a/queries/match_participation.sql b/queries/match_participation.sql
index d37983f..43785c6 100644
--- a/queries/match_participation.sql
+++ b/queries/match_participation.sql
@@ -1,23 +1,23 @@
 SELECT
 
-id AS "participant_id",
-relationships->'player'-> 'data' ->> 'id' AS player_id,
+relationships->'player'->'data'->>'id' AS player_id,
+0 AS "match_id",
 0 AS "rosterId",
 0 AS "fk_player_id",
 0 AS "fk_match_results_id",
 attributes->>'actor' AS "hero",
-(attributes->'stats'->>'kills')::int AS "kills",
+COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
 (attributes->'stats'->>'deaths')::int AS "deaths",
 (attributes->'stats'->>'assists')::int AS "assists",
 0.0 AS "KD",
 0.0 AS "KDA",
 0 AS "killParticipation",
-(attributes->'stats'->>'nonJungleMinionKills')::int AS "laneMinionsSlayed",
-(attributes->'stats'->>'minionKills')::int AS "jungleMinionsSlayed",
-(attributes->'stats'->>'turretCaptures')::int AS "turretsDestroyed",
-(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
-(attributes->'stats'->>'goldMineCaptures')::int AS "goldMineCaptures",
-(attributes->'stats'->>'crystalMineCaptures')::int AS "crystalMineCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'nonJungleMinionKills', ''), '0')::int AS "laneMinionsSlayed",
+COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "jungleMinionsSlayed",
+COALESCE(NULLIF(attributes->'stats'->>'turretCaptures', ''), '0')::int AS "turretsDestroyed",
+COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
 (attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk",
 FALSE AS "perfectGame"
 
-- 
cgit v1.3.1

