JonnyP Posts - Answer Overflow

JonnyP

Explore posts from servers

•Created by JonnyP on 7/30/2023 in #help

Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?

Hi There, I am currently investigating Drizzle ORM and seeing if I can bend it to do everything I need 😅 I quickly got into making queries that both return nested JSON properties (User Including Posts Including Comments etc) but also wanting to use the power of SQL to do some summations / transform values etc. Context: - My Play Around Repo: https://github.com/JonParton/drizzle-local-playground - The Schema: https://github.com/JonParton/drizzle-local-playground/blob/master/src/schema.ts I started with the "new" Drizzle Relational querie's api(https://orm.drizzle.team/docs/rqb) which is awesome for the nested JSON returns, but I was struggeling with how to then dig in deeper if you wanted to say do a summation of values across one of the nested values... I saw the extras property was for this but I struggled as the aliases for the joined tables get made automatically and I coudln't find a property to access their names so you end up having to hardcode them:

   const usersWithPostsAndComments = await db.query.user.findMany({
     with: {
       posts: {
         with: {
           comments: true,
         },
       },
     },
     extras: (table) => ({
       totalPostStars: sql
         .raw(`sum("user_posts"."starRating")`) // Would love not to hard code this ...
         .as("totalPostStars"),
     }),
   });

   const usersWithPostsAndComments = await db.query.user.findMany({
     with: {
       posts: {
         with: {
           comments: true,
         },
       },
     },
     extras: (table) => ({
       totalPostStars: sql
         .raw(`sum("user_posts"."starRating")`) // Would love not to hard code this ...
         .as("totalPostStars"),
     }),
   });

So after that I started seeing if I could take more control and use the brialliant jsonAgg helpers that @Raphaël Moreau shared in his gist https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15

19 replies

Gaming

Programming