From bf9743589beb4d91263e1dfaf9c2b74288d7fbd2 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Sun, 5 Mar 2017 16:33:31 +0100
Subject: enable shardid and fix timestamp

---
 queries/player.sql | 8 ++++++--
 1 file changed, 6 insertions(+), 2 deletions(-)

(limited to 'queries/player.sql')

diff --git a/queries/player.sql b/queries/player.sql
index 585858d..412bf40 100644
--- a/queries/player.sql
+++ b/queries/player.sql
@@ -1,12 +1,15 @@
 WITH rosters AS (SELECT
-  match.data->'data'->'attributes'->>'created_at' AS matchdate,
+  match.data->'data'->'attributes'->>'shardId' AS shardid,
+  (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate,
   JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
 FROM match WHERE id=$1),
 participants AS (SELECT
+  shardid,
   matchdate,
   JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
 FROM rosters),
 players AS (SELECT
+  shardid,
   matchdate,
   JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player
 FROM participants)
@@ -14,6 +17,7 @@ FROM participants)
 SELECT
 
 players.player->'data'->>'id' AS "api_id",
+shardid AS "shard_id",
 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",
@@ -22,7 +26,7 @@ players.player->'data'->'attributes'->>'name' AS "name",
 (players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_casual",
 (players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins",
 (players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetime_gold",
-CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "last_match_created_date",
+CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp END AS "last_match_created_date",
 
 0 AS "streak"
 
-- 
cgit v1.3.1

