Drizzle Team•4w ago

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?

9 Replies

Patrik•4w ago

im not sure i understood you right but db.select its plain object you can manipulate it how you want

.select({
  ...getTableColumns(reviews),
  performance: {
    time: performances.time,
    artist: {
      ...getTableColumns(artists),
    },
    venue: {
      ...getTableColumns(venues),
    },
  },
  reviewer: {
    firstName: users.firstName,
    lastName: users.lastName,
  },
  commentsCount: ctx.db
    .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
    .as('commentsCount'),
  })
})

.select({
  ...getTableColumns(reviews),
  performance: {
    time: performances.time,
    artist: {
      ...getTableColumns(artists),
    },
    venue: {
      ...getTableColumns(venues),
    },
  },
  reviewer: {
    firstName: users.firstName,
    lastName: users.lastName,
  },
  commentsCount: ctx.db
    .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
    .as('commentsCount'),
  })
})

like that if that wont work try to set columns without function getTableColumns but it should work

ayyjohnOP•2w ago

taking a look 🙂 thank you!

ayyjohnOP•2w ago

ok, yeah so what you had is what I tried initially, and at least at a static analysis level it complained 😦 is that potentially a drizzle bug, or maybe something I need to fix in my schema/relations? I have the following relations for artists/performances

export const artistsRelations = relations(artists, ({ many }) => ({
  performances: many(performances),
}))

export const performancesRelations = relations(
  performances,
  ({ one, many }) => ({
    artist: one(artists, {
      fields: [performances.artistId],
      references: [artists.id],
    }),
    tour: one(tours, {
      fields: [performances.tourId],
      references: [tours.id],
    }),
    venue: one(venues, {
      fields: [performances.venueId],
      references: [venues.id],
    }),
    reviews: many(reviews),
  }),
)

export const artistsRelations = relations(artists, ({ many }) => ({
  performances: many(performances),
}))

export const performancesRelations = relations(
  performances,
  ({ one, many }) => ({
    artist: one(artists, {
      fields: [performances.artistId],
      references: [artists.id],
    }),
    tour: one(tours, {
      fields: [performances.tourId],
      references: [tours.id],
    }),
    venue: one(venues, {
      fields: [performances.venueId],
      references: [venues.id],
    }),
    reviews: many(reviews),
  }),
)

Patrik•2w ago

how ur func getTableColumns looks like perhaps its just TS error? have you checked it works or no?

ayyjohnOP•2w ago

getTableColumns is from drizzle-orm but I can try updating my drizzle version. I haven't tried actually running it 😅 let me do that sigh okay lmao it works even with the errors i'll try upgrading/reinstalling drizzle to see if I can get it to go away, and if not, submit an issue I think cause unfortunately, even though it's working, my types think everything's broken 😦 looks like i'm on the current highest version of drizzle-orm sob i still feel like I have a syntax error... hold on ahhhhh, yeah I think i figured it out: it doesn't like me naming that field artist because my relations say that performance.artist has a specific type so changing it to

.select({
  ...getTableColumns(reviews),
  performance: {
    time: performances.time,
    artistName: artists.name
    venueName: venues.name
  },
  reviewer: {
    firstName: users.firstName,
    lastName: users.lastName,
  },
  commentsCount: ctx.db
    .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
    .as('commentsCount'),
  })
})

.select({
  ...getTableColumns(reviews),
  performance: {
    time: performances.time,
    artistName: artists.name
    venueName: venues.name
  },
  reviewer: {
    firstName: users.firstName,
    lastName: users.lastName,
  },
  commentsCount: ctx.db
    .$count(reviewComments, eq(reviewComments.reviewId, reviews.id))
    .as('commentsCount'),
  })
})

works i still feel like I should be able to do the original syntax, but at least I know why it's complaining now so I can see if I can either fix that by fixing my relations or work around it

ayyjohnOP•2w ago

oooooh, I think maybe the error might be coming from the use of the generic word name ? cause if I simplify it to just

...
artist: { 
  name: artists.name
}
...

...
artist: { 
  name: artists.name
}
...

I get this more specific error and clicking through the name key takes me to drizzle's Column type def

ayyjohnOP•2w ago

though notably that doesn't happen if i add name: performances.name to the performances object

ayyjohnOP•2w ago

ok, yeah looks like it's the same issue as https://github.com/drizzle-team/drizzle-orm/issues/2050

GitHub

[BUG]: Nested object in select query · Issue #2050 · drizzle-team/d...

What version of drizzle-orm are you using? 0.29.3 What version of drizzle-kit are you using? 0.20.14 Describe the Bug My code is const feedbacks = await drizzleClient .select({ ...getTableColumns(f...

ayyjohnOP•2w ago

figured that out by changing the column name to stageName and getting the same error as that ticket

Gaming

Programming

how to nest select statement to achieve aliased result

Did you find this page helpful?