Prisma•2w ago

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;

result is in the attached image

4 Replies

RaphaelEtim•2w ago

Hi @Mohammad Orabi 🇱🇧 Can you please share your schema file? We have also have users report that the coaleasce function return the wrong type in typedSQL as can be seen in this open issue. As typedSQL is in preview, these kind of issue can be expected so that we can make improvements to it.

GitHub

Issues · prisma/prisma

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB - Issues · prisma/prisma

Mohammad Orabi 🇱🇧OP•2w ago

here is my related schemas

model Chest {
  id          String          @id @default(cuid()) @map("id")
  name        String          @map("name")
  imageUrl    String?         @map("image_url")
  description String?         @map("description")
  rarity      Rarity          @map("rarity")
  amount      Int?            @map("amount")
  gameId      String          @map("game_id")
  game        Game            @relation(fields: [gameId], references: [id], onDelete: Cascade)
  currencies  ChestCurrency[]
  cosmetics   ChestCosmetic[]
  createdAt   DateTime        @default(now()) @map("created_at")
  updatedAt   DateTime        @updatedAt @map("updated_at")

  @@unique([gameId, rarity])
  @@index([gameId])
  @@map("chests")
}

model ChestKeys {
  id        String   @id @default(cuid()) @map("id")
  basic     Int      @default(0) @map("basic")
  premium   Int      @default(0) @map("premium")
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  gameId    String   @map("game_id")
  game      Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@unique([userId, gameId])
  @@index([userId])
  @@index([gameId])
  @@map("chest_keys")
}

model Chest {
  id          String          @id @default(cuid()) @map("id")
  name        String          @map("name")
  imageUrl    String?         @map("image_url")
  description String?         @map("description")
  rarity      Rarity          @map("rarity")
  amount      Int?            @map("amount")
  gameId      String          @map("game_id")
  game        Game            @relation(fields: [gameId], references: [id], onDelete: Cascade)
  currencies  ChestCurrency[]
  cosmetics   ChestCosmetic[]
  createdAt   DateTime        @default(now()) @map("created_at")
  updatedAt   DateTime        @updatedAt @map("updated_at")

  @@unique([gameId, rarity])
  @@index([gameId])
  @@map("chests")
}

model ChestKeys {
  id        String   @id @default(cuid()) @map("id")
  basic     Int      @default(0) @map("basic")
  premium   Int      @default(0) @map("premium")
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  gameId    String   @map("game_id")
  game      Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@unique([userId, gameId])
  @@index([userId])
  @@index([gameId])
  @@map("chest_keys")
}

RaphaelEtim•2w ago

Thanks for sharing, I’m taking a look

Mohammad Orabi 🇱🇧OP•2w ago

Thank you very much appreciate it yes I think its an important issue , as I was forced to cast my type when querying it for typescript to shutup so that defeats the whole purpose of typedSQL haha

Gaming

Programming

TypedSql generating wrong types from the query