Drizzle Team•17mo ago

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

12 Replies

JonnyPOP•17mo ago

However, I quickly found I needed to start using sub queries to allow easy referencing of values at the top level and allow json nesting within nesting. However, this lead me to then need to use jsonAgg on a sub query and I got stuffed by types:

const postsWithComments = db.$with("postsWithComments").as(
    db
      .select({
        ...getTableColumns(post),
        userStarRating: sql<number>`sum(${comment.userStarRating})`.as(
          "userStarRating"
        ),
        countOfUserStarRatings:
          sql<number>`count(${comment.userStarRating})`.as(
            "countOfUserStarRatings"
          ),
        averageUserStarRating:
          sql<number>`sum(${comment.userStarRating})/count(${comment.userStarRating})`.as(
            "averageUserStarRating"
          ),
        comments: jsonAgg(comment).as("comments"),
      })
      .from(post)
      .leftJoin(comment, eq(post.id, comment.postId))
      .groupBy(post.id)
  );

  const usersWithPostsAndComments = await db

    .with(postsWithComments)
    .select({
      ...getTableColumns(user),
      totalPostRatings: sql<number>`sum(${postsWithComments.starRating})`.as(
        "totalPostRatings"
      ),
      averageUserPostRatings:
        sql<number>`sum(${postsWithComments.starRating})/count(${postsWithComments.starRating})`.as(
          "averageUserPostRatings"
        ),
      totalCommentRatings:
        sql<number>`sum(${postsWithComments.userStarRating})`.as(
          "totalCommentRatings"
        ),
      averageUserCommentRatings:
        sql<number>`sum(${postsWithComments.userStarRating})/sum(${postsWithComments.countOfUserStarRatings})`.as(
          "averageUserCommentRatings"
        ), // Can't use avg here as we need details on how many comments hae been sumised!
      posts: jsonAgg(postsWithComments).as("posts"), // This breaks our typescript here, however the jsonAgg function won't take a subquery only a table... Even though it does actually work runtime wise.
    })
    .from(user)
    .leftJoin(postsWithComments, eq(user.id, postsWithComments.authorId))
    .groupBy(user.id)
    .limit(2);

const postsWithComments = db.$with("postsWithComments").as(
    db
      .select({
        ...getTableColumns(post),
        userStarRating: sql<number>`sum(${comment.userStarRating})`.as(
          "userStarRating"
        ),
        countOfUserStarRatings:
          sql<number>`count(${comment.userStarRating})`.as(
            "countOfUserStarRatings"
          ),
        averageUserStarRating:
          sql<number>`sum(${comment.userStarRating})/count(${comment.userStarRating})`.as(
            "averageUserStarRating"
          ),
        comments: jsonAgg(comment).as("comments"),
      })
      .from(post)
      .leftJoin(comment, eq(post.id, comment.postId))
      .groupBy(post.id)
  );

  const usersWithPostsAndComments = await db

    .with(postsWithComments)
    .select({
      ...getTableColumns(user),
      totalPostRatings: sql<number>`sum(${postsWithComments.starRating})`.as(
        "totalPostRatings"
      ),
      averageUserPostRatings:
        sql<number>`sum(${postsWithComments.starRating})/count(${postsWithComments.starRating})`.as(
          "averageUserPostRatings"
        ),
      totalCommentRatings:
        sql<number>`sum(${postsWithComments.userStarRating})`.as(
          "totalCommentRatings"
        ),
      averageUserCommentRatings:
        sql<number>`sum(${postsWithComments.userStarRating})/sum(${postsWithComments.countOfUserStarRatings})`.as(
          "averageUserCommentRatings"
        ), // Can't use avg here as we need details on how many comments hae been sumised!
      posts: jsonAgg(postsWithComments).as("posts"), // This breaks our typescript here, however the jsonAgg function won't take a subquery only a table... Even though it does actually work runtime wise.
    })
    .from(user)
    .leftJoin(postsWithComments, eq(user.id, postsWithComments.authorId))
    .groupBy(user.id)
    .limit(2);

So I started trying to create a jsonAgg function that could handle a subquery but the figuring out the rigth types to return stumped me and I couldn't find API's to help.

export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
  return sql<
    InferModel<Table>[]
  >`coalesce(json_agg(${table}) filter (where ${table} is not null), '[]')`;
}

export function jsonAggSubQuery<Subquery>(subquery: Subquery) {
  return sql<
    Awaited<Subquery>[]// This isn't right! the awaited results of a db query end up including `SQL.Aliased<number>` rather then just `number`
  >`coalesce(json_agg(${subquery}) filter (where ${subquery} is not null), '[]')`;
}

export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
  return sql<
    InferModel<Table>[]
  >`coalesce(json_agg(${table}) filter (where ${table} is not null), '[]')`;
}

export function jsonAggSubQuery<Subquery>(subquery: Subquery) {
  return sql<
    Awaited<Subquery>[]// This isn't right! the awaited results of a db query end up including `SQL.Aliased<number>` rather then just `number`
  >`coalesce(json_agg(${subquery}) filter (where ${subquery} is not null), '[]')`;
}

Is there an api or a method someone has created to handle sub queries like this ... basically looking for a InferModel<SubQuery> api! Or if someone has an idea of how I should better go about this please fire away! For reference I did bomb this old thread (https://discord.com/channels/1043890932593987624/1093581666666156043/1135127584477286530) with my queries too ... Moved here to try and be well behaved!

iukea•16mo ago

Going to have to forgive me on this. I normally don't store JSON docs in SQL But I think what you're looking for is JSONb format I remember you were able to play with that type of stuff in postgres And JSON has a hard time with searching inside the nested JSON but a format called JSONB could be parsed and indexed Or . ....I could be making this up I try so hard to stay away from nested JSON So JSON vs jsonb JSON = everything from the db view in postgresSQL (I think mySQL also) is viewed as one big string JSONB is viewed as one big array in a cell that can be indexed for. Again

JonnyPOP•16mo ago

So just to be clear, all my Schema is flat normal database tables with relations and no JSON fields - I am just using either relational queries or jsonAgg to make the return from the database include nested elements. So in terms of typescript I just want to be able to access them via indexes and keys... No need for specific jsonB queries etc. Ie should be able to do usersReturn[0].posts[0].comments[0].author_id for example and have typescript be happy!

iukea•16mo ago

I appreciate trying to understand your problem more. Because I know I might face it in the future

JonnyPOP•16mo ago

Bump!...

The Knopp•13mo ago

Hi. Honestly I am at least satisfied, that someone faces the same problem as me. Drizzle seems great for inference from simple queries, but the types of subqueries are a mess and I can't find a solution for that.

JonnyPOP•12mo ago

@Andrew Sherman - so sorry for the tag.... I know a while back you mentioned to tag you in any threads that got missed when you were snowed under... This is one of those threads!

Andrii Sherman•12mo ago

I guess let's bring @Dan Kochetov here, we can help with types

Angelelz•12mo ago

This type of complex queries are not available yet in the RQB I'll work on json helpers when I finish mssql implementation

rphlmr ⚡•12mo ago

Looks like https://discord.com/channels/1043890932593987624/1181877312296341524 no?

Angelelz•12mo ago

Yep, I think it has to be done with the CRUD API

rphlmr ⚡•12mo ago

A little bit too magic for me haha but at some point we just need it works

Gaming

Programming

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