Could someone help me with one to many relation in prisma?

I have a cron job that create a list of records from different tables. Those table items need to be linked (have foreign key) with record of tables. Following is my Prisma schema and the cron api.
Solution:
```tsx const savedReportDB = await prisma.reportDatabse.create({ data: { userId, generatedFor: yesterday,...
Jump to solution
16 Replies
albatroz
albatroz2y ago
model User {
id String @id @default(uuid())

WeightDatabase WeightDatabase[]
LiterDatabase LiterDatabase[]
PieceCountingDatabase PieceCountingDatabase[]
GradeDatabase GradeDatabase[]
ReportDatabse ReportDatabse[]
AlertDatabase AlertDatabase[]

GradeSystemTemplate GradeSystemTemplate[]
PieceCountingTemplate PieceCountingTemplate[]
AlertSystemTemplate AlertSystemTemplate[]
}


model ReportDatabse {
reportId String @id @unique @default(uuid())
user User @relation(fields: [userId], references: [id])
userId String
generatedFor DateTime

savedWeight WeightDatabase[]
savedLiter LiterDatabase[]
savedPieceCounting PieceCountingDatabase[]
savedGrades GradeDatabase[]
AlertDatabase AlertDatabase[]
ReportDatabseAverages ReportDatabaseAverages?

@@index([userId, generatedFor(sort: Asc), reportId], name: "reportDatabase_comp_key")
}

model ReportDatabaseAverages {
id String @id @unique @default(uuid())
report ReportDatabse @relation(fields: [reportId], references: [reportId])
reportId String @unique

savedWeightAverage Float? @default(0)
savedLiterAverage Float? @default(0)
}
model User {
id String @id @default(uuid())

WeightDatabase WeightDatabase[]
LiterDatabase LiterDatabase[]
PieceCountingDatabase PieceCountingDatabase[]
GradeDatabase GradeDatabase[]
ReportDatabse ReportDatabse[]
AlertDatabase AlertDatabase[]

GradeSystemTemplate GradeSystemTemplate[]
PieceCountingTemplate PieceCountingTemplate[]
AlertSystemTemplate AlertSystemTemplate[]
}


model ReportDatabse {
reportId String @id @unique @default(uuid())
user User @relation(fields: [userId], references: [id])
userId String
generatedFor DateTime

savedWeight WeightDatabase[]
savedLiter LiterDatabase[]
savedPieceCounting PieceCountingDatabase[]
savedGrades GradeDatabase[]
AlertDatabase AlertDatabase[]
ReportDatabseAverages ReportDatabaseAverages?

@@index([userId, generatedFor(sort: Asc), reportId], name: "reportDatabase_comp_key")
}

model ReportDatabaseAverages {
id String @id @unique @default(uuid())
report ReportDatabse @relation(fields: [reportId], references: [reportId])
reportId String @unique

savedWeightAverage Float? @default(0)
savedLiterAverage Float? @default(0)
}
model WeightDatabase {
weight Float
weightId String @id @default(uuid())

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "weightDatabase_comp_key")
}

model PieceCountingDatabase {
pieceId String @id @default(uuid())
itemName String @db.VarChar(255)
singlePieceWeight Float
itemsCounted Float

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "pieceCountingDatabase_comp_key")
}

model GradeDatabase {
itemName String
gradeId String @id @default(uuid())
gradeName String
gradeUpperLimit Float
gradeLowerLimit Float
gradedItemWeight Float

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "gradeDatabase_comp_key")
}
model WeightDatabase {
weight Float
weightId String @id @default(uuid())

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "weightDatabase_comp_key")
}

model PieceCountingDatabase {
pieceId String @id @default(uuid())
itemName String @db.VarChar(255)
singlePieceWeight Float
itemsCounted Float

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "pieceCountingDatabase_comp_key")
}

model GradeDatabase {
itemName String
gradeId String @id @default(uuid())
gradeName String
gradeUpperLimit Float
gradeLowerLimit Float
gradedItemWeight Float

createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
userId String @unique

ReportDatabse ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
reportDatabseReportId String?

@@index([createdAt(sort: Asc), userId], name: "gradeDatabase_comp_key")
}
And this is how handler looks like
const AlertDatabase = await prisma.alertDatabase.findMany({
where: {
userId,
createdAt: {
gte: yesterday,
lt: new Date(),
},
},
});

const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: savedWeight },
savedLiter: { connect: savedLiter },
savedPieceCounting: { connect: savedPieceCounting },
savedGrades: { connect: savedGrades },
AlertDatabase: { connect: AlertDatabase },
},
});

console.log(
"===> ~ file: reportDatabase.ts:121 ~ savedReportDB:",
savedReportDB,
);

await prisma.reportDatabaseAverages.create({
data: {
reportId: savedReportDB.reportId,
savedWeightAverage,
savedLiterAverage,
},
});
const AlertDatabase = await prisma.alertDatabase.findMany({
where: {
userId,
createdAt: {
gte: yesterday,
lt: new Date(),
},
},
});

const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: savedWeight },
savedLiter: { connect: savedLiter },
savedPieceCounting: { connect: savedPieceCounting },
savedGrades: { connect: savedGrades },
AlertDatabase: { connect: AlertDatabase },
},
});

console.log(
"===> ~ file: reportDatabase.ts:121 ~ savedReportDB:",
savedReportDB,
);

await prisma.reportDatabaseAverages.create({
data: {
reportId: savedReportDB.reportId,
savedWeightAverage,
savedLiterAverage,
},
});
But it's not connected to any of the records
albatroz
albatroz2y ago
abcdef
abcdef2y ago
@Albatros Try to change it to
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: { id: savedWeight.id } },
savedLiter: { connect: { id: savedLiter.id } },
savedPieceCounting: { connect: { id: savedPieceCounting.id } },
savedGrades: { connect: { id: savedGrades.id } },
AlertDatabase: { connect: { id: AlertDatabase.id } },
},
});
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: { id: savedWeight.id } },
savedLiter: { connect: { id: savedLiter.id } },
savedPieceCounting: { connect: { id: savedPieceCounting.id } },
savedGrades: { connect: { id: savedGrades.id } },
AlertDatabase: { connect: { id: AlertDatabase.id } },
},
});
albatroz
albatroz2y ago
But, savedWeight is an array. saved** are array's
albatroz
albatroz2y ago
abcdef
abcdef2y ago
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: savedWeight.map(({ id }) => ({ id })) },
savedLiter: { connect: { id: savedLiter.id } },
savedPieceCounting: { connect: { id: savedPieceCounting.id } },
savedGrades: { connect: { id: savedGrades.id } },
AlertDatabase: { connect: { id: AlertDatabase.id } },
},
});
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: { connect: savedWeight.map(({ id }) => ({ id })) },
savedLiter: { connect: { id: savedLiter.id } },
savedPieceCounting: { connect: { id: savedPieceCounting.id } },
savedGrades: { connect: { id: savedGrades.id } },
AlertDatabase: { connect: { id: AlertDatabase.id } },
},
});
@Albatros
albatroz
albatroz2y ago
albatroz
albatroz2y ago
Got prisma error
abcdef
abcdef2y ago
Yeah I updated the code
Solution
abcdef
abcdef2y ago
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: {
connect: savedWeight.map((weight) => ({ weightId: weight.weightId })),
},
savedLiter: {
connect: savedLiter.map((liter) => ({ literId: liter.literId })),
},
savedPieceCounting: {
connect: savedPieceCounting.map((piece) => ({ pieceId: piece.pieceId })),
},
savedGrades: {
connect: savedGrades.map((grade) => ({ gradeId: grade.gradeId })),
},
AlertDatabase: {
connect: AlertDatabase.map((alert) => ({ alertId: alert.alertId })),
},
},
});
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,
savedWeight: {
connect: savedWeight.map((weight) => ({ weightId: weight.weightId })),
},
savedLiter: {
connect: savedLiter.map((liter) => ({ literId: liter.literId })),
},
savedPieceCounting: {
connect: savedPieceCounting.map((piece) => ({ pieceId: piece.pieceId })),
},
savedGrades: {
connect: savedGrades.map((grade) => ({ gradeId: grade.gradeId })),
},
AlertDatabase: {
connect: AlertDatabase.map((alert) => ({ alertId: alert.alertId })),
},
},
});
^^ If they're all arrays
albatroz
albatroz2y ago
Thanks, let's check if they work Praype a lot man thankyou i ask one last favor? https://app.serverlessq.com/ i am using this for cron, how can i make this a private request
albatroz
albatroz2y ago
tried to log headers, but x-api-key is not present there
abcdef
abcdef2y ago
You want to limit access to the endpoint with an api key?
albatroz
albatroz2y ago
Yes
abcdef
abcdef2y ago
You can use a middleware I'll send an example, hold on
/* Middleware - middleware/validateApiKey.ts */
export const validateApiKey = (handler) => async (req, res) => {
const apiKey = req.headers["x-api-key"];

if (!apiKey) {
return res.status(401).json({ message: "Missing API key" });
}

const expectedApiKey = process.env.API_KEY;

if (apiKey !== expectedApiKey) {
return res.status(401).json({ message: "Invalid API key" });
}

return handler(req, res);
};



/* pages/api/route.ts */
import { validateApiKey } from "middleware/validateApiKey";

async function handler(req: NextApiRequest, res: NextApiResponse) {
// ...
}

export default validateApiKey(handler);
/* Middleware - middleware/validateApiKey.ts */
export const validateApiKey = (handler) => async (req, res) => {
const apiKey = req.headers["x-api-key"];

if (!apiKey) {
return res.status(401).json({ message: "Missing API key" });
}

const expectedApiKey = process.env.API_KEY;

if (apiKey !== expectedApiKey) {
return res.status(401).json({ message: "Invalid API key" });
}

return handler(req, res);
};



/* pages/api/route.ts */
import { validateApiKey } from "middleware/validateApiKey";

async function handler(req: NextApiRequest, res: NextApiResponse) {
// ...
}

export default validateApiKey(handler);
Something like this should work
albatroz
albatroz2y ago
Ok trying this yup that worked
Want results from more Discord servers?
Add your server