https://www.prisma.io/ logo
Join Slack
Powered by
SOLUTION IN THE REPLIES I'm trying to get a subse...
# orm-help
c
SOLUTION IN THE REPLIES I'm trying to get a subset of records back filtering on a array within the array of records. license = 3
Copy code
const whereClause = {
        {
          License: {
            every: {
              LicenseTypeID: license
            }
          }
        }
    };
Regardless of whether I user every: or some: it pulls all the records. There is no filtering going on.
Copy code
return Promise.all([
      this.prisma.person.findMany({
        take: perPage,
        skip,
        where: whereClause,
        include: defaultIncludeQuery
      })
r
@Chip Clark 👋 Could you share your schema so that I can check? Using 
some
in the filter should give the 
person
records that have the 
LicenseTypeID
equal to 3.
c
Copy code
model License {
  LicenseID        Int         @id @default(autoincrement())
  PKPersonID       Int
  LicenseTypeID    Int
  LicenseNumber    String?     @db.VarChar(30)
  LicenseYear      String?     @db.VarChar(4)
  LicenseState     String?     @db.VarChar(2)
  LicenseStateLong String?     @db.VarChar(50)
  LicenseDate      DateTime?   @db.Date
  Note             String?     @db.VarChar(Max)
  Active           Boolean
  ActiveFromDate   DateTime    @db.Date
  ModifiedDate     DateTime    @db.DateTime
  ModifiedBy       String      @db.VarChar(30)
  ValidFromDate    DateTime
  ValidToDate      DateTime
  LicenseType      LicenseType @relation(fields: [LicenseTypeID], references: [LicenseTypeID])
  Person           Person      @relation(fields: [PKPersonID], references: [PKPersonID])
}  

model Person {
  PKPersonID                Int                   @id @default(autoincrement())
  /// The value of this field is generated by the database as: `newsequentialid()`.
  PersonGUID                String                @db.UniqueIdentifier
  LastName                  String                @db.VarChar(30)
  FirstName                 String                @db.VarChar(30)
  MiddleName                String?               @db.VarChar(30)
  PreferredFirstName        String?               @db.VarChar(30)
  DisplayName               String?               @db.VarChar(60)
  IsEmployee                Boolean?
  Note                      String?               @db.VarChar(Max)
  Active                    Boolean
  OfficeFloorID             Int?                  @db.TinyInt
  BiographyURL              String?               @db.VarChar(300)
  HasBiography              Boolean
  HRDepartment              HRDepartment?         @relation(fields: [HRDepartmentID], references: [HRDepartmentID])
  JobTitle                  JobTitle?             @relation(fields: [JobTitleID], references: [JobTitleID])
  OfficeLocation            OfficeLocation?       @relation(fields: [OfficeLocationID], references: [OfficeLocationID])
  License                   License[]
  PersonCommittee           PersonCommittee[]       
  Phone                     Phone[]

  @@index([PersonGUID], name: "ncl_Person_PersonGUID")
}
Any thoughts as to why "some" doesn't limit the return as expected?
r
I cannot reproduce this. Filtering the Licence type using 
some
with sample data that I entered always gives me records that have those in the relation. Btw, 
some
only filters the items at the top level and not what you specify in 
include
. 
include
will always include all the items in the relation. To filter inside 
include
, you can use 
where
.
c
OK, that seems to be the problem - because the include is: 
export const defaultIncludeQuery: Prisma.PersonInclude = {
  
License: {
    
select: {
      
LicenseID: true,
      
PKPersonID: true,
      
LicenseTypeID: true,
      
LicenseNumber: true,
      
LicenseYear: true,
      
LicenseState: true,
      
LicenseStateLong: true,
      
LicenseDate: true
    
}
  
},
How to I get it to show ONLY the records via the filter AND show the License information?
I put this in the include 
License: {
          select: {             LicenseID: true,             PKPersonID: true,             where: {                   LicenseTypeID: license             },             LicenseNumber: true,             LicenseYear: true,             LicenseState: true,             LicenseStateLong: true,             LicenseDate: true           } but it's still pulling all the users with licenses.
r
You need to have both. 
some
in the main query and 
where
in 
select
.
c
Finally got this working: I need to do the following: Create a variable for the whereclause 
const whereClause = {
License: {
              
some: {
                
LicenseTypeID: 3
                
}
              
}
}
Then create an include: 
const includeClause = {
license: {}
}
Because I wanted to be able to filter on the LicenseTypeID. I needed to create a series of IF statements where I could parse IF parameter is 2, set whereClause variable accordingly. Because I always want to include the License information, the includeClause can remain static. The some is necessary in the where, but NOT in the include. However, I cannot use select in the includeClause to limit the fields displayed. If I do, prisma pulls ALL records with license information, regardless of the whereClause.
r
You can do the same with 
select
c
Can you offer an example showing this? Because I don't understand how. Thanks,
r
Copy code
this.prisma.person.findMany({
        take: perPage,
        skip,
        where: whereClause,
        select: defaultIncludeQuery
      })
This should work. So basically it would be this:
Copy code
select: {
  License: {
    LicenseTypeID: true,
    where: { LicenseTypeID: 3 }
  }
}
c
Ref: #general - Oct 4th tried:
Copy code
Person: {
            select: {
              LastName: true,
              FirstName: true,
              MiddleName: true,
              PreferredFirstName: true,
              DisplayName: true,
              EmploymentStatus: true,
              where: {
                EmploymentStatus: 'A'
              }
but get this error
Copy code
Unknown field `where` for select statement on model Person. Available options are listed in green.
Invalid `this.prisma.person.findMany()` invocation in
C:\Sites\AM-API-MDD\dist\app\person\person.service.js:447:32
4 Views
Open in Slack
PreviousNext
Powered by