JonnyP
JonnyP
Explore posts from servers
KKyselyDTDrizzle Team
PostsComments
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
@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!
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
Bump!...
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
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!
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
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!
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
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!
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
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);
19 replies
DTDrizzle Team
Created by JonnyP on 7/30/2023 in #help
Unwrap the return type of a Subquery, native jsonAgg support or RQ Join Names?
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:
19 replies