TypedSql generating wrong types from the query

* DESCRIPTION
My query returns the chests from the chest table and add to it the numOfKeys the user has for those chests by joining the chestKeys table, while returning the chests , if the user does not have a chestKey record I am explicitly falling back to 0 instead of null

* ISSUE
TypedSql is returning numOfKeys as number | null

* EXPECTED RESULT
TypedSql should return numOfKeys as number

* QUERY

-- @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;

-- @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;

result is in the attached image

TypedSql generating wrong types from the query

Similar Threads

Similar Threads

Similar Threads