How does one implement Prisma's features like count relations and nested reads?

Get Help and Help Others
1

Hello!

I have been actively using Redwood for a week now for a hobby project and it has been a great learning experience for me as a general programming novice. The tutorial has been especially helpful!

I have done a deep dive into Prisma’s docs and found some features I wanted to try. I tried using db.posts.count() and it worked once I made changes to my service and sdl files (used the pagination tutorial as a guide). Right now, I am confused about how I could implement other features like count relations and nested reads. What changes would have to be made to the SDL, service, and cell to have a query that returns a list of posts that each has its number of comments?

I have tried something like this:

export const posts = () => {
  return db.post.findMany({
    include: {
      _count: {
        select: { comments: true },
      },
    },
  })
}

But I know I need to do more than that since there is no way for me to access the count parameter as I did not include it in the type. I’m guessing nested reads can be done on the web side by specifying the relations and field in the cell’s query but just wanted to confirm.

I appreciate any insight!

1 Like
2

Hi There,

I’m not sure I can give you definitive answers for these questions, but I can start with db.$queryRaw – which has enabled things for me that I could not figure out how to do in prisma

I’ve used db.$queryRaw – using raw sql to get the job done

    const attendeeCounts = await db.$queryRaw`select
      count(*) as total,
      count(case when "twoFACode" is not null then 1 else null end) as registered,
      count(case when "answerStatus" is true then 1 else null end) as proven
    from "Attendee" where "eventId" = ${evt.id}`

and this where I had to use the db.$queryRawUnsafe to pull data from a View

    const selectFromView = `SELECT * FROM "CitizenView" WHERE ${whereByStr} ${orderByStr} LIMIT ${pageSize} OFFSET ${rowOffset}`
    logger.debug(`[${__file}] ~ selectFromView: ${selectFromView}`)
    let rows = await db.$queryRawUnsafe(selectFromView)

On to your questions:

here’s code I used to count guests related to an event. Since it’s a one-to-many (guests-to-event) the relation in prisma has an eventId on the guest side and an array of guests on the event side

I counted from the guest side

    const guestCount = await db.guest
      .count({ where: { eventId } })
      .then(thenDebug(`assertWithinEventCapacity ~ db.guest.count`))

this is code where I counted the rows that were deleted

      await db.attendeeAnswer
        .deleteMany({
          where: { id: { in: answerIds } },
        })
        .then(thenDebug('db.attendeeAnswer.deleteMany'))
        .then((result) => {
          deletionResults = {
            attendeeAnswerDeleted: result.count,
            ...deletionResults,
          }
        })
        .catch(logger.error)

as to your other question: I’m guessing nested reads can be done on the web side by specifying the relations and field in the cell’s query but just wanted to confirm.

this cell’s query


export const QUERY = gql`
  query EventPersonListQuery($eventId: String!) {
    attendees(eventId: $eventId) {
      id
      event {
        id
        name
        proofRequired
        canGuests
        canForward
        inquiries {
          id
        }
      }
      user {
        email
      }
      answers {
        id
        inquiryId
        proofRequired
        image {
          id
          name
        }
        answer
      }
      answerStatus
      name
      email
      phone
      twoFACode
      description
      hasGuests
      guests {
        id
      }
      forwards {
        id
      }
    }
  }
`

is answered with this code

  return await db.attendee
    .findMany({
      where: { eventId, AND: { userId: context.currentUser.id } },
    })
    .then(thenDebug(`[${__file}] db.attendee.findMany`))
    .catch(logger.error)

as far as I understand it (always learning) the relations are filled via the scaffolded Attendee

export const Attendee = {
  agreement: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).agreement(),
  event: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).event(),
  user: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).user(),
  answers: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).answers(),
  forwards: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).forwards(),
  guests: (_obj, { root }: ResolverArgs<ReturnType<typeof attendee>>) =>
    db.attendee.findUnique({ where: { id: root.id } }).guests(),
}

when I generated the sdl for Attendee the relations had been defined in Prisma

and the relevant prisma for the Attendee

model Attendee {
  id            String           @id
  eventId       String
  [...]
  event         Event            @relation(fields: [eventId], references: [id], onDelete: Cascade)
  guests        Guest[]
}

model Guest {
  id           String        @id
  eventId      String
  attendeeId   String
  attendee     Attendee      @relation(fields: [attendeeId], references: [id], onDelete: Cascade)
  event        Event         @relation(fields: [eventId], references: [id], onDelete: Cascade)
}

model Event {
  id             String     @id
  [...]
  attendees      Attendee[]
  guests         Guest[]
}

I hope this has helped.

Al;

  • redwood rules !!
1 Like
3

Thank you so much for the comprehensive answer! I will definitely be using raw sql to build out a custom field and will come back to your examples to guide me :slight_smile:

I suddenly realized that I could use JSON as a type and added that to the SDL to access the count relations result

// api/src/post.sdl.js

 type Post {
    id: Int!
    [...]
   _count: JSON
  }

With the code I posted earlier, I was able to access the count in the cell query!

1 Like