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/player.sql | 34 ++++++++++++++++++++++++----------
 1 file changed, 24 insertions(+), 10 deletions(-)

(limited to 'queries/player.sql')

diff --git a/queries/player.sql b/queries/player.sql
index 992cb44..0619ee6 100644
--- a/queries/player.sql
+++ b/queries/player.sql
@@ -1,15 +1,29 @@
+WITH rosters AS (SELECT
+  match.data->'data'->'attributes'->>'createdAt' AS matchdate,
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1),
+participants AS (SELECT
+  matchdate,
+  JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
+FROM rosters),
+players AS (SELECT
+  matchdate,
+  JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player
+FROM participants)
+
 SELECT
 
-id AS "apiId",
-attributes->>'name' AS "name",
-(attributes->'stats'->>'level')::int AS "level",
-(attributes->'stats'->>'xp')::int AS "xp",
-(attributes->'stats'->>'played')::int AS "played",
-(attributes->'stats'->>'played_ranked')::int AS "playedRanked",
-(attributes->'stats'->>'played')::int - (attributes->'stats'->>'played_ranked')::int AS "playedCasual",
-(attributes->'stats'->>'wins')::int AS "wins",
-(attributes->'stats'->>'lifetimeGold')::float AS "lifetimeGold",
+players.player->'data'->>'id' AS "apiId",
+players.player->'data'->'attributes'->>'name' AS "name",
+(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level",
+(players.player->'data'->'attributes'->'stats'->>'xp')::int AS "xp",
+(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played",
+(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedRanked",
+(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedCasual",
+(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins",
+(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetimeGold",
+CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "lastMatchCreatedDate",
 
 0 AS "streak"
 
-FROM "player"
+FROM players
-- 
cgit v1.3.1

