-- @name getMyChestsQuery
-- @param {String} $1:userId The ID of the user
-- @param {String} $2:gameId The ID of the game
WITH user_keys AS (
SELECT basic,
premium,
game_id
FROM chest_keys
WHERE user_id = $1
AND game_id = $2
)
SELECT c.id,
c.name,
c.rarity,
c.game_id as "gameId",
c.description,
c.image_url as "imageUrl",
COALESCE(
CASE
c.rarity
WHEN 'basic' THEN COALESCE(uk.basic, 0)
WHEN 'premium' THEN COALESCE(uk.premium, 0)
END,
0
)::integer as "numOfKeys",
COALESCE(
(
SELECT jsonb_agg(
jsonb_build_object(
'type',
'currency',
'name',
curr.name,
'imageUrl',
curr.image_url,
'probabilities',
cc.probabilities
)
)
FROM chest_currencies cc
JOIN currencies curr ON cc.currency_id = curr.id
WHERE cc.chest_id = c.id
),
'[]'::jsonb
) as rewards
FROM chests c
LEFT JOIN user_keys uk ON uk.game_id = c.game_id
WHERE c.game_id = $2
ORDER BY CASE
c.rarity
WHEN 'basic' THEN 1
WHEN 'premium' THEN 2
END;
-- @name getMyChestsQuery
-- @param {String} $1:userId The ID of the user
-- @param {String} $2:gameId The ID of the game
WITH user_keys AS (
SELECT basic,
premium,
game_id
FROM chest_keys
WHERE user_id = $1
AND game_id = $2
)
SELECT c.id,
c.name,
c.rarity,
c.game_id as "gameId",
c.description,
c.image_url as "imageUrl",
COALESCE(
CASE
c.rarity
WHEN 'basic' THEN COALESCE(uk.basic, 0)
WHEN 'premium' THEN COALESCE(uk.premium, 0)
END,
0
)::integer as "numOfKeys",
COALESCE(
(
SELECT jsonb_agg(
jsonb_build_object(
'type',
'currency',
'name',
curr.name,
'imageUrl',
curr.image_url,
'probabilities',
cc.probabilities
)
)
FROM chest_currencies cc
JOIN currencies curr ON cc.currency_id = curr.id
WHERE cc.chest_id = c.id
),
'[]'::jsonb
) as rewards
FROM chests c
LEFT JOIN user_keys uk ON uk.game_id = c.game_id
WHERE c.game_id = $2
ORDER BY CASE
c.rarity
WHEN 'basic' THEN 1
WHEN 'premium' THEN 2
END;