Help with findFirst and none relations
I'm working on seeding my database for a self storage and I'm trying to randomly select a customer with no leases and assign them a lease. This is my code and it's alway returning the same customer:
const customer = await prisma.user.findFirst({
where: {
customerLeases: {
none: {}
}
},
select: {
id: true,
contactInfo: true,
},
});
When I seed the database one customer ends up with all the leases. I assume from the docs that this is how I should be searching for a customer with no leases so I don't understand what I'm doing wrong. Any help would be greatly appreciated5 Replies
In the prisma service add this code on constructor:
this.$on('query', e => {
console.log('Query: ' + e.query);
console.log('Params: ' + e.params);
console.log('Duration: ' + e.duration + 'ms');
});
And then paste the queries that are running in the database here to help to understanding the problem
And paste the database structure from schema and the part of seed code
My schema parts:
model User {
id String @id @default(cuid())
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime? @updatedAt @map("updated_at")
email String @unique
emailVerified DateTime? @map("email_verified")
customerLeases Lease[]
contactInfo ContactInfo[]
paymentMade PaymentRecord[]
customerInvoices Invoice[]
employee Employee?
session Session[]
account Account[]
@@unique([id, email])
@@index([id, email])
@@map("users")
}\
model ContactInfo {
id String @id @default(cuid())
user User @relation(fields: [email], references: [email], onUpdate: NoAction)
email String
givenName String
familyName String
organizationName String?
address1 String
address2 String?
address3 String?
city String
state String
zip String
phoneNum1 String
phoneNum2 String?
leases Lease[]
phoneNum1Validated Boolean @default(false)
phoneNum2Validated Boolean? @default(false)
softDelete Boolean @default(false)
}
model Lease {
id String @id @default(cuid())
customer User @relation(fields: [customerId], references: [id], onDelete: NoAction, onUpdate: NoAction)
customerId String
employee Employee @relation(fields: [employeeId], references: [userId], onDelete: NoAction, onUpdate: NoAction)
employeeId String
contactInfo ContactInfo @relation(fields: [contactInfoId], references: [id], onUpdate: NoAction, onDelete: NoAction)
contactInfoId String
unitPrice UnitPricing @relation(fields: [price, unitNum], references: [price, unitNum], onUpdate: NoAction)
unitNum String
price Int
leaseCreatedAt DateTime @default(now())
leaseReturnedAt DateTime?
leaseEffectiveDate DateTime
leaseEnded DateTime?
invoices Invoice[]
@@unique([id, unitNum, price])
@@index([id, leaseCreatedAt(sort: Desc)])
}
the createLease function
async function createLease(unit: UnitPricing, leaseStart: Date, leaseEnd: Date) {
const employees = await prisma.employee.findMany({
select: { userId: true },
});
const employeeList: string[] = [];
employees.forEach((employee) => { employeeList.push(employee.userId); });
const employee = employees[Math.floor(Math.random() * employees.length)];
const customer = await prisma.user.findFirst({
where: {
customerLeases: {
none: {}
}
},
select: {
id: true,
contactInfo: true,
},
});
let leaseEnded:Date | null = null;
if (leaseEnd) { leaseEnded = new Date(leaseEnd); }
const lease = await prisma.lease.create({
data: {
customerId: customer!.id,
employeeId: employee.userId,
contactInfoId: customer!.contactInfo[0].id,
unitNum: unit.unitNum,
price: unit.price,
leaseEffectiveDate: new Date(leaseStart),
leaseEnded,
},
});
return lease;
}
and how that gets call in the main() function
pricedUnits.forEach(async (unit) => {
const unitLeases: Date[] = Array.from({ length: Math.floor(Math.random() * maxLeases) + 1 });
let leaseStart = new Date(earliestStarting);
const today = new Date;
let numMonthsLeft = monthDif(leaseStart, today);
unitLeases.forEach(async () => {
const lengthOfLease = Math.floor(Math.random() * numMonthsLeft) + 1;
let leaseEnd = new Date(addMonths(leaseStart, lengthOfLease));
if (leaseEnd > today || monthDif(leaseEnd, today) < 3) { leaseEnd = null; }
leases.push(await createLease(unit, leaseStart, leaseEnd));
leaseStart = addMonths(leaseEnd, 1);
numMonthsLeft = monthDif(leaseStart, today);
});
});
and the last few querrys:
Query: SELECT "public"."users"."id", "public"."users"."email" FROM "public"."users" WHERE ("public"."users"."id") NOT IN (SELECT "t1"."customerId" FROM "public"."Lease" AS "t1" WHERE (1=1 AND "t1"."customerId" IS NOT NULL)) LIMIT $1 OFFSET $2
Params: [1,0]
Duration: 1ms
Query: SELECT "public"."ContactInfo"."id", "public"."ContactInfo"."email","public"."ContactInfo"."givenName","public"."ContactInfo"."familyName","public"."ContactInfo"."organizationName","public"."ContactInfo"."address1", "public"."ContactInfo"."address2", "public"."ContactInfo"."address3","public"."ContactInfo"."city","public"."ContactInfo"."state","public"."ContactInfo"."zip","public"."ContactInfo"."phoneNum1","public"."ContactInfo"."phoneNum2","public"."ContactInfo"."phoneNum1Validated","public"."ContactInfo"."phoneNum2Validated", "public"."ContactInfo"."softDelete" FROM "public"."ContactInfo" WHERE "public"."ContactInfo"."email" IN ($1) OFFSET $2
Params: ["[email protected]",0]
Duration: 1ms
Query: SELECT "public"."ContactInfo"."id", "public"."ContactInfo"."email", "public"."ContactInfo"."givenName", "public"."ContactInfo"."familyName", "public"."ContactInfo"."organizationName", "public"."ContactInfo"."address1","public"."ContactInfo"."address2","public"."ContactInfo"."address3","public"."ContactInfo"."city","public"."ContactInfo"."state","public"."ContactInfo"."zip","public"."ContactInfo"."phoneNum1", "public"."ContactInfo"."phoneNum2", "public"."ContactInfo"."phoneNum1Validated", "public"."ContactInfo"."phoneNum2Validated", "public"."ContactInfo"."softDelete" FROM "public"."ContactInfo" WHERE "public"."ContactInfo"."email" IN ($1) OFFSET $2
Params: ["[email protected]",0]
Duration: 0ms
Query: SELECT "public"."users"."id", "public"."users"."email" FROM "public"."users" WHERE ("public"."users"."id") NOT IN (SELECT "t1"."customerId" FROM "public"."Lease" AS "t1" WHERE (1=1 AND "t1"."customerId" IS NOT NULL)) LIMIT $1 OFFSET $2
Params: [1,0]
Duration: 2msIt really looks like correctly, the query filter users that doesn't have leases, the findFirst every time returns the first if the where doesn't match anything.
"The subquery SELECT "t1"."customerId" FROM "public"."Lease" AS "t1" WHERE (1=1 AND "t1"."customerId" IS NOT NULL) returns all customerId from the Lease table where customerId is not null.
The NOT IN clause in the WHERE of the main query filters users whose id is not in the list of customerId returned by the subquery.
If there are no records in the Lease table (or if all customerId in the Lease table are null), the subquery will return an empty set.
If the subquery returns an empty set, the NOT IN clause will evaluate as true for all records in the users table (all users will be considered as not having leases)."
Consider get all the users as you do for employees and random it to get one
But to resolve the problem i think that is necessary more information like:
Which users are in database?
Which is returned by query in each iteration?
The user that is returned in each iteration came with the leases? ( Try to select the leases to validate )
Try to transform the forEach into for await and divide the call functions like:
for await(const unit of pricedUnits) {
const createdLease = await createLease(unit, leaseStart, leadeEnd);
leases.push(createdLease);
} and post the result of queries of full run of seed
It seems to have been an issue with awaiting and Array.forEach(). Refactoring to for await fixed it.
Thanks
Nice