我真无语啊, 这种 bug, 在怎么小心, 还是踩进去了.
事情是这样的, 在一个 API 接口上增加个查询条件一不小心, 在最后多用了个 where, 把前面的 where 条件都覆盖了, 我当前知道二个where条件要用到 andWhere, 但是没想到分修的条件也不能直接用 where. 上代码吧
const query: SelectQueryBuilder<Component> = this.componentRepo.createQueryBuilder("component")
.leftJoinAndSelect("component.fae", "user")
.leftJoinAndSelect("user.userDepament", "departments")
.leftJoinAndSelect("component.brand", "brand")
.leftJoinAndSelect("component.partNo", "partNo")
.leftJoinAndSelect('component.statusDate', 'statusDate')
.leftJoinAndSelect("component.info", "project")
.leftJoinAndSelect('project.customer', 'customer')
.leftJoinAndSelect('project.app', 'app')
.leftJoinAndSelect('app.appClass', 'appClass')
.where((new Brackets(qb => {
if (!isSave) {
qb.where("user.employeeId = :employeeId", { employeeId })
}
})))
.andWhere("project.type=1")
.andWhere((new Brackets(qb => {
if (startDate || endDate) {
// 时间 where 条件
qb.where("statusDate.promotionDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dinDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.samplerunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pilotrunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.mpDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pendingDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.lostDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dwinDate BETWEEN :startDate AND :endDate")
}
})))
.andWhere(new Brackets(qb => {
// 应用名ids
if (appIds && appIds.length > 0) {
qb.where("app.id IN (:...appIds)", { appIds })
}
// 厂牌ids
if (brandIds && brandIds.length > 0) {
qb.where("brand.id IN (:...brandIds)", { brandIds })
}
// 客户ids
if (customerIds && customerIds.length > 0) {
qb.where("customer.id IN (:...customerIds)", { customerIds })
}
// 料号ids
if (partNoIds && partNoIds.length > 0) {
qb.where("partNo.id IN (:...partNoIds)", { partNoIds })
}
}))
.select([
'component.id',
'app.id', 'app.chname', 'app.egname',
'user.id', 'user.employeeId', 'user.chname', 'user.egname', 'user.egname',
'departments.id', 'departments.name', 'departments.twName', 'departments.egName',
'appClass.id', 'appClass.chname', 'appClass.egname',
'brand.id', 'brand.name',
'customer.id', 'customer.chname', 'customer.egname',
'component.amt', 'project.id'
])
// 添加时间
if (startDate || endDate) {
query.addSelect("COUNT(CASE WHEN DATE(statusDate.promotionDate) BETWEEN :startDate AND :endDate THEN 1 END)", "promotion")
.addSelect("COUNT(CASE WHEN DATE(statusDate.dinDate) BETWEEN :startDate AND :endDate THEN 1 END)", "din")
.addSelect("COUNT(CASE WHEN DATE(statusDate.samplerunDate) BETWEEN :startDate AND :endDate THEN 1 END)", "samplerun")
.addSelect("COUNT(CASE WHEN DATE(statusDate.pilotrunDate) BETWEEN :startDate AND :endDate THEN 1 END)", "pilotrun")
.addSelect("COUNT(CASE WHEN DATE(statusDate.mpDate) BETWEEN :startDate AND :endDate THEN 1 END)", "mp")
.addSelect("COUNT(CASE WHEN DATE(statusDate.pendingDate) BETWEEN :startDate AND :endDate THEN 1 END)", "pending")
.addSelect("COUNT(CASE WHEN DATE(statusDate.lostDate) BETWEEN :startDate AND :endDate THEN 1 END)", "lost")
.addSelect("COUNT(CASE WHEN DATE(statusDate.dwinDate) BETWEEN :startDate AND :endDate THEN 1 END)", "dwin")
.addSelect("COUNT(CASE WHEN statusDate.status <=2 THEN 1 END)", "alive")
.setParameters({
startDate,
endDate
})
} else {
query.addSelect("COUNT(CASE WHEN statusDate.promotionDate THEN 1 END)", "promotion")
.addSelect("COUNT(CASE WHEN statusDate.dinDate THEN 1 END)", "din")
.addSelect("COUNT(CASE WHEN statusDate.samplerunDate THEN 1 END)", "samplerun")
.addSelect("COUNT(CASE WHEN statusDate.pilotrunDate THEN 1 END)", "pilotrun")
.addSelect("COUNT(CASE WHEN statusDate.mpDate THEN 1 END)", "mp")
.addSelect("COUNT(CASE WHEN statusDate.pendingDate THEN 1 END)", "pending")
.addSelect("COUNT(CASE WHEN statusDate.lostDate THEN 1 END)", "lost")
.addSelect("COUNT(CASE WHEN statusDate.dwinDate THEN 1 END)", "dwin")
.addSelect("COUNT(CASE WHEN statusDate.status <=2 THEN 1 END)", "alive")
}
const components = await query.groupBy("component.id").getRawMany();
上面代码是修正过的代码下面这个.
.andWhere((new Brackets(qb => {
if (startDate || endDate) {
// 时间 where 条件
qb.where("statusDate.promotionDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dinDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.samplerunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pilotrunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.mpDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pendingDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.lostDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dwinDate BETWEEN :startDate AND :endDate")
}
})))
以上代码之前是这样写的. 见下.用了 query
if (startDate || endDate) {
// 时间 where 条件
query.where("statusDate.promotionDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dinDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.samplerunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pilotrunDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.mpDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.pendingDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.lostDate BETWEEN :startDate AND :endDate")
.orWhere("statusDate.dwinDate BETWEEN :startDate AND :endDate")
}
很明显没有放在 andWhere 里面去, 然后, 就算用了 andWhere 也是错的, 只为同一个 or 条件, 没有放在一个子集里面去.
