ayyjohn Posts - Answer Overflow

ayyjohn

•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