ayyjohn Posts - Answer Overflow

ayyjohn

•Created by ayyjohn on 1/27/2025 in #help

Tightening types on successful insert to not be `object | undefined`

When doing db.insert(someTable).values(someValues).returning() what would be the preferred way to narrow the type of the return value from object | undefined if the insert is successful? I'm trying to do some seeding where I create some entries in one table and save them as variables, and then attempt to insert the IDs of those entries into another table as a foreign key that's non-nullable and TS tells me that they're potentially undefined, which is true. Since this is seeding, I know I'm clearing my DB before this, so I know the inserts will succeed and that it's not an issue if they don't) so would the best way to silence these errors just be to add ! to the objects when doing returnedObject!.id? more generally, I'm curious if there's a drizzle specific way to chain anything onto the .values({...}).returning() call that will narrow the type so that undefined isn't an option. I didn't see anything in the docs that would do this. I tried adding onConflictDoNothing and onConflictDoUpdate to see if they'd narrow the type but they don't. Is there something in the docs I'm missing, or alternatively has anybody written an extension method for PgInsertBuilder (or any of the other builders) so that you could do something like const [row] = await db.insert(table).values({}).returning().assertSuccess() and then the type of row couldn't be undefined? I know that this is basically the same as adding if (!row) throw new Error("failed to insert row") after each insert, I was just curious if there's a better way.

1 replies

DTDrizzle Team

•Created by ayyjohn on 12/30/2024 in #help

how to nest select statement to achieve aliased result

if I have a basic findMany with nested relations and the following schema * users create reviews (as reviewer) * reviews are of a performance * performances are by an artist * performances are at a venue

const recentReviews = await ctx.db.query.reviews.findMany({
      orderBy: (reviews, { desc }) => [desc(reviews.createdAt)],
      limit: 10,
      with: {
        performance: {
          with: {
            artist: true,
            venue: true,
          },
        },
        reviewer: true,
      },
    })

const recentReviews = await ctx.db.query.reviews.findMany({
      orderBy: (reviews, { desc }) => [desc(reviews.createdAt)],
      limit: 10,
      with: {
        performance: {
          with: {
            artist: true,
            venue: true,
          },
        },
        reviewer: true,
      },
    })

that returns the data like

[
  {
    reviewInfo, 
    performance: { 
      performanceInfo, 
      artist: artistInfo, 
      venue: venueInfo 
    }, 
    reviewer: userInfo
  }
]

[
  {
    reviewInfo, 
    performance: { 
      performanceInfo, 
      artist: artistInfo, 
      venue: venueInfo 
    }, 
    reviewer: userInfo
  }
]

and i'm trying to perform an aggregation to get the top 10 reviews created in the the last 10 days with the most comments

const popularReviews = await ctx.db
      .select({
        ...getTableColumns(reviews),
        performance: {
          time: performances.time,
        },
        reviewer: {
          firstName: users.firstName,
          lastName: users.lastName,
        },
        venue: {
          ...getTableColumns(venues),
        },
        artist: {
          ...getTableColumns(artists),
        },
        commentsCount: ctx.db
          .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
          .as('commentsCount'),
      })
      .from(reviews)
      .innerJoin(users, eq(users.id, reviews.reviewerId))
      .innerJoin(performances, eq(performances.id, reviews.performanceId))
      .innerJoin(artists, eq(artists.id, performances.artistId))
      .innerJoin(venues, eq(venues.id, performances.venueId))
      .orderBy((t) => desc(t.commentsCount))
      .where(
        gte(reviews.createdAt, DateTime.now().minus({ days: 10 }).toJSDate()),
      )
      .limit(10)

const popularReviews = await ctx.db
      .select({
        ...getTableColumns(reviews),
        performance: {
          time: performances.time,
        },
        reviewer: {
          firstName: users.firstName,
          lastName: users.lastName,
        },
        venue: {
          ...getTableColumns(venues),
        },
        artist: {
          ...getTableColumns(artists),
        },
        commentsCount: ctx.db
          .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
          .as('commentsCount'),
      })
      .from(reviews)
      .innerJoin(users, eq(users.id, reviews.reviewerId))
      .innerJoin(performances, eq(performances.id, reviews.performanceId))
      .innerJoin(artists, eq(artists.id, performances.artistId))
      .innerJoin(venues, eq(venues.id, performances.venueId))
      .orderBy((t) => desc(t.commentsCount))
      .where(
        gte(reviews.createdAt, DateTime.now().minus({ days: 10 }).toJSDate()),
      )
      .limit(10)

which almost gives me back the same data shape, but it won't let me nest artist and venue info under performance. is there a way to update my joins or my select statement so that the shape will be the same as the relational query?

23 replies

Gaming

Programming