Prisma•5mo ago

Nested relation query help

Hello, I was trying to query users, then I added the implementation to show payments but now I realize it was bringing payments of all users from the membership instead of bringing all payments of that specific user for each specific membership. Is there a way to do this query in the same object or is there a way to reference the current user for each index of the array?

prisma.user.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    memberships: {
      include: {
        payments: {
          take: 1,
          orderBy: {
            endDate: 'asc'
          }
        }
      }
    }
  }
})

prisma.user.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    memberships: {
      include: {
        payments: {
          take: 1,
          orderBy: {
            endDate: 'asc'
          }
        }
      }
    }
  }
})

Example of what It would be ideal but ofc it doesn't work

prisma.user.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    memberships: {
      include: {
        payments: {
          where: {
            memberId: "current_member_id??"
          },
          take: 1,
          orderBy: {
            endDate: 'asc'
          }
        }
      }
    }
  }
})

prisma.user.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    memberships: {
      include: {
        payments: {
          where: {
            memberId: "current_member_id??"
          },
          take: 1,
          orderBy: {
            endDate: 'asc'
          }
        }
      }
    }
  }
})

3 Replies

moosthuizen•5mo ago

@Fervore Would you post your schema.prisma?

FervoreOP•5mo ago

hello @moosthuizen, thanks for reaching out, here are the models in question from the schema.prisma

model Member {
    id String @id() @default(cuid())
    name String
    email String?
    phone Int?
    sex String
    DNI String
    memberships Membership[]
    payments Payment[]
    orgId String
    organization Organization @relation(fields: [orgId], references: [id])
}

model Membership {
    id String @id() @default(cuid())
    period Int
    cost Int
    name String
    members Member[]
    payments Payment[]
    authorId String
    createdBy User @relation(fields: [authorId], references: [id])
    orgId String
    organization Organization @relation(fields: [orgId], references: [id])
}

model Payment {
    id String @id() @default(cuid())
    startDate DateTime @default(now())
    endDate DateTime
    membership Membership @relation(fields: [membershipId], references: [id])
    membershipId String
    amount Int @default(0)
    member Member @relation(fields: [memberId], references: [id])
    memberId String
    createdBy User @relation(fields: [authorId], references: [id])
    authorId String
    createdAt DateTime @default(now())
}

model Organization {
    id String @id() @default(cuid())
    name String
    createdAt DateTime @default(now())
    slug String
    members Member[]
    users User[] @relation("organization")
    ownerId String @unique()
    owner User @relation("owns", fields: [ownerId], references: [id])
    memberships Membership[]
}

model Member {
    id String @id() @default(cuid())
    name String
    email String?
    phone Int?
    sex String
    DNI String
    memberships Membership[]
    payments Payment[]
    orgId String
    organization Organization @relation(fields: [orgId], references: [id])
}

model Membership {
    id String @id() @default(cuid())
    period Int
    cost Int
    name String
    members Member[]
    payments Payment[]
    authorId String
    createdBy User @relation(fields: [authorId], references: [id])
    orgId String
    organization Organization @relation(fields: [orgId], references: [id])
}

model Payment {
    id String @id() @default(cuid())
    startDate DateTime @default(now())
    endDate DateTime
    membership Membership @relation(fields: [membershipId], references: [id])
    membershipId String
    amount Int @default(0)
    member Member @relation(fields: [memberId], references: [id])
    memberId String
    createdBy User @relation(fields: [authorId], references: [id])
    authorId String
    createdAt DateTime @default(now())
}

model Organization {
    id String @id() @default(cuid())
    name String
    createdAt DateTime @default(now())
    slug String
    members Member[]
    users User[] @relation("organization")
    ownerId String @unique()
    owner User @relation("owns", fields: [ownerId], references: [id])
    memberships Membership[]
}

moosthuizen•5mo ago

@Fervore Seeing as there is a relation between Member and Payment, would the following work?

const result = prisma.member.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    payments: {
      distinct:[membershipId], // Only get one payment for each membership
      take: 1,
      orderBy: { endDate: 'asc' },
      include: {
        membership: true
      }
    }
  }
})

const result = prisma.member.findMany({
  orderBy: {
    id: 'desc',
  },
  include: {
    payments: {
      distinct:[membershipId], // Only get one payment for each membership
      take: 1,
      orderBy: { endDate: 'asc' },
      include: {
        membership: true
      }
    }
  }
})

You can then flatten result[].payments[].membership into result[].new_property_with_membership_and_payment_fields or similar Alternatively, my recommendation is to use the first query you listed above, and do the filtering you want in code.

Gaming

Programming

Nested relation query help