我有两个表客户和业务，我希望能够在一个查询中从两个表获取。
Customer Model(父表)
model business {
id Int @id @default(autoincrement())
customer_id Int
registered_no String?
business_name String? @unique
subscription_name String? @db.VarChar(100)
name_of_plan String?
name_of_plaza String? @db.Text
state String? @db.VarChar(35)
city String?
street_name String?
street_no String?
lga String?
area String?
location String? @db.VarChar(55)
is_coverage Boolean @default(false)
created_at DateTime @default(now())
updated_at DateTime @default(now())
customer customer @relation(fields: [customer_id], references: [id])
@@index([customer_id, registered_no])
}
业务模型(子表)
model customer {
id Int @id @default(autoincrement())
unique_identifier String? @unique @db.VarChar(155)
od_id String? @db.VarChar(10)
surname String? @db.VarChar(55)
firstname String? @db.VarChar(55)
middlename String? @db.VarChar(55)
othernames String? @db.VarChar(55)
phone String @db.VarChar(13)
mobile String? @db.VarChar(13)
email_address String @unique @db.VarChar(155)
home_address String @db.Text
state String? @db.VarChar(35)
city String?
street_name String?
street_no String?
lga String?
area String?
location String? @db.VarChar(55)
status Int?
customer_type String? @db.VarChar(9)
country_code String? @db.MediumText
date_of_birth String? @db.VarChar(15)
how_did_you_hear String? @db.VarChar(55)
created_at DateTime @default(now())
updated_at DateTime @default(now())
business business[]
@@index([unique_identifier, phone, mobile])
}
基本上，每当用户在业务模型中搜索记录并根据用户输入的搜索参数加入客户模型时，它应该显示业务记录，并包括搜索参数与客户和业务数据匹配的客户记录。
这是我当前的查询:
async getAllCustomers (req, res) {
const records_per_page = Number(req.query.records_per_page) || 20;
const current_page = Math.max(Number(req.query.page_number || 1), 1)
let recordCount
const options = {
take: records_per_page, //total number of items on a pge
skip: (current_page - 1) * records_per_page,
orderBy: { id: 'desc' },
}
options.select = {
subscription_name: true,
business_name: true,
name_of_plan: true,
customer: {
select: {
id: true,
unique_identifier: true,
phone: true,
mobile: true,
email_address: true,
home_address: true
},
},
}
const countOptions = {}
if(req.query.filter){
const filter = req.query.filter
if(filter === "business_internet"){
options.where = {
subscription_name: { in: [ BusinessCategories.DualPlay, BusinessCategories.TriplePlay, BusinessCategories.TriplePlayX ] }
}
}else if(filter === "business_wifi"){
options.where = {
subscription_name: { equals: BusinessCategories.BusinessWifi }
}
}
countOptions.where = options.where
}
if(req.query.search){
**//I know there is something wrong here but I can't seems to find what exactly**
options.select = {
customer: {
where: {
email_address: { contains: req.query.search }
}
}
}
// options.where = {
// OR: [
// { email_address: { contains: req.query.search }},
// { surname: { contains: req.query.search }},
// { firstname: { contains: req.query.search }},
// { state: { contains: req.query.search }},
// ],
// }
// countOptions.where = options.where
}
let customers = await prisma.business.findMany(options)
recordCount = await prisma.business.count(countOptions)
res.status(200).json({data: customers ,
pagination: {
current_page: current_page,
totalCount: recordCount,
pageCount: Math.ceil(recordCount / records_per_page)
}
})
}
我知道有什么问题与请求。查询。搜索代码块，但我似乎不能找出它是什么。请告诉我该怎么做。
在进一步深入研究这个问题之后，我已经能够修复这个问题。下面是我为搜索参数
添加的代码片段if(req.query.search){
options.where = {
customer: {
OR: [
{phone: {contains: req.query.search }},
{surname: { contains: req.query.search }},
{email_address: { contains: req.query.search }},
]
},
}
countOptions.where = options.where
}
参见此处的参考:https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#filter-on--to-many-relations