Trying to filter WHERE on subtable of a subtable Comittee is Prisma #orm-help

Trying to filter WHERE on subtable of a subtable ...

Chip Clark

10/05/2021, 2:12 AM

Trying to filter WHERE on subtable of a subtable Comittee is the primary table - PersonCommittee is a subtable, where Person is a subtable of PersonCommittee. The following doesn't return an error, but it does return data that does not meet the criteria.

Copy code

this.prisma.committee.findMany({
        include: {
          PersonCommittee: {
            include: {
              Person: {
                select: {
                  PKPersonID: true,
                  LastName: true,
                  FirstName: true,
                  MiddleName: true,
                  PreferredFirstName: true,
                  DisplayName: true,
                  EmploymentStatus: true
                  }
                }
              }
            }
        },
        where: {
          AND: [
            {
              Active: true,
              PersonCommittee: {
                some: {
                  Person: {
                    EmploymentStatus: "A" 
                  }
                }
              }
            }
          ]
        },
      })

Ryan

10/05/2021, 6:51 AM

@Chip Clark Try it by using

AND

and

OR

separately like this (also this will filter only the parent i.e. committee and not inside the nested relations)

Copy code

this.prisma.committee.findMany({
        include: {
          PersonCommittee: {
            include: {
              Person: {
                select: {
                  PKPersonID: true,
                  LastName: true,
                  FirstName: true,
                  MiddleName: true,
                  PreferredFirstName: true,
                  DisplayName: true,
                  EmploymentStatus: true
                  }
                }
              }
            }
        },
        where: {
          Active: true,
          OR: [
            { PersonCommittee: { some: { Person: { EmploymentStatus: 'A' } } } },
            { PersonCommittee: { some: { Person: { EmploymentStatus: 'B' } } } },
            { PersonCommittee: { some: { Person: { EmploymentStatus: 'C' } } } },
          ],
        }
      })

Chip Clark

10/05/2021, 5:43 PM

I implemented this and I'm still getting results where EmploymentStatus = "T" - which is what I'm trying to avoid.

Ryan

10/06/2021, 5:26 AM

Does the SQL generated match the above statement?

Chip Clark

10/11/2021, 10:04 PM

yes

Chip Clark

10/12/2021, 12:42 AM

I have another senario where I'm getting all employees. The data returned isn't filtering. The data structure model Person { PKPersonID Int @id @default(autoincrement()) PersonGUID String @db.UniqueIdentifier LastName String @db.VarChar(30) FirstName String @db.VarChar(30) MiddleName String? @db.VarChar(30) EmploymentStatus String? @db.Char(1) SupportedPerson PersonRelationship[] @relation(name: "SupportedPerson") @@index([PersonGUID], name: "ncl_Person_PersonGUID") } model PersonRelationship { PersonRelationshipID Int @id @default(autoincrement()) PKPersonID Int RelationshipTypeID Int RelatedPersonID Int SupportedPerson Person @relation(name: "SupportedPerson", fields: [RelatedPersonID], map: "SupportedPersons", references: [PKPersonID], onDelete: NoAction, onUpdate: NoAction) Person Person @relation(name: "PrimaryPerson", fields: [PKPersonID], references: [PKPersonID], onDelete: NoAction, onUpdate: NoAction) @@unique([PKPersonID, RelatedPersonID, RelationshipTypeID], name: "nclunq_PersonRelationship_PKPersonID_RelatedPersonID_RelationshipTypeID") } The section below works with regard to RelationshipTypeID but not for Person. This is the portion of the include that doesn't work: SupportedPerson : { where: { OR: [ { RelationshipTypeID: 1 }, { RelationshipTypeID: 4 }, { RelationshipTypeID: 5 }, { Person: { EmploymentStatus: 'A' } } ] },

Chip Clark

10/13/2021, 7:28 PM

I have tried

Copy code

SupportedPerson: {
              some: {
                  Person: { EmploymentStatus: "A" }
              }
            }

Chip Clark

10/13/2021, 7:29 PM

and

Copy code

SupportedPerson: {
              Person: {
                  some: { EmploymentStatus: "A" }
              }
            }

Chip Clark

10/13/2021, 7:29 PM

and

Copy code

SupportedPerson: {
              Person: {
                  none: { EmploymentStatus: "T" }
              }
            }

Chip Clark

10/13/2021, 7:32 PM

The last one removes all records, shows nothing. some: Person - displays all records (no filter) Person: some - gives an error:

Copy code

Unknown arg `Person` in where.AND.4.SupportedPerson.Person for type PersonRelationshipListRelationFilter. Did you mean `every`?

Chip Clark

10/13/2021, 7:43 PM

SOLUTION: in the include statement:

Copy code

SupportedPerson : {
        where: {
          OR: [
            {
              RelationshipTypeID: 1
            },
            {
              RelationshipTypeID: 4
            },
            {
              RelationshipTypeID: 5
            },
          ],
          AND: [
              {
                Person: {
                    EmploymentStatus: 'A' 
                }
              },
          ]
        },

10 Views

Open in Slack

Previous Next