K
Kysely•2y ago
suchcodemuchwow

Wrong return type in raw sql

Greetings I have simple aggregations on mysql: 
select([
   sql`COUNT(DISTINCT r.review_id)`.as("reviewCount"),
   sql`COUNT(DISTINCT i.interaction_id)`.as("interactionCount"),
   sql<number>`COUNT(DISTINCT r.review_id) * 4 + COUNT(DISTINCT i.interaction_id)`.as("score")
])
select([
   sql`COUNT(DISTINCT r.review_id)`.as("reviewCount"),
   sql`COUNT(DISTINCT i.interaction_id)`.as("interactionCount"),
   sql<number>`COUNT(DISTINCT r.review_id) * 4 + COUNT(DISTINCT i.interaction_id)`.as("score")
])
Even if I specify <number> the return type is coming back as string when I check with typeof reviewCount and in other. Is there a way to typecast raw sql queries, rather than modifying the executed query results or not 🤔
Solution:
Kysely doesn't do any data transformations. The underlying driver has full control over that. Some drivers might not unwrap numeric values of result columns that might exceed javascript's safe number range. If the driver returns column metadata with the results, you could extend the dialect and automatically unwrap specific result columns by data type. If not, you can implement a custom plugin that allows per-query transformation of result columns given in a list....
Jump to solution
5 Replies
Igal
Igal•2y ago
Hey 👋 What dialect are you using?
suchcodemuchwow
suchcodemuchwowOP•2y ago
Hello 🤗 Planetscale dialect
Solution
Igal
Igal•2y ago
Kysely doesn't do any data transformations. The underlying driver has full control over that. Some drivers might not unwrap numeric values of result columns that might exceed javascript's safe number range. If the driver returns column metadata with the results, you could extend the dialect and automatically unwrap specific result columns by data type. If not, you can implement a custom plugin that allows per-query transformation of result columns given in a list.
Igal
Igal•2y ago
btw, Kysely has built-in aggregate functions. 
select(({ fn, ref }) => [
  fn.count('r.review_id').distinct().as('reviewCount'),
  fn.count('i.interaction_id').distinct().as('interactionCount'),
  sql<string>`count(distinct ${ref('r.review_id')}) * 4 + count(distinct ${ref('i.interaction_id')})`.as('score')
])
select(({ fn, ref }) => [
  fn.count('r.review_id').distinct().as('reviewCount'),
  fn.count('i.interaction_id').distinct().as('interactionCount'),
  sql<string>`count(distinct ${ref('r.review_id')}) * 4 + count(distinct ${ref('i.interaction_id')})`.as('score')
])
suchcodemuchwow
suchcodemuchwowOP•2y ago
Wooow thanks a lot for the suggestions Igal, it helped a lot ❤️

Did you find this page helpful?