找到即将到期的资格证书

Question

我有以下函数，它返回StaffQualifications，其中要么是限定到期，要么是限定类型过期，但是这个部分似乎不太适合我，因为它分割了string.这是否应该是两个独立的函数，即使95%的查询是相同的？

    if ($qualification instanceof Qualification) {
        $sql .= "AND q.id = :qualificationId ";
    } elseif ($qualificationType instanceof QualificationType) {
        $sql .= "AND q.qualification_type_id = :qualificationTypeId ";
    }

上面的感觉不太好，但不知道如何更好。

/**
 * @return StaffQualification[]
 */
public function getStaffQualificationsExpiringOnQualificationOrQualificationType(?Qualification $qualification, ?QualificationType $qualificationType): array
{
    if ($qualification === null && $qualificationType === null) {
        throw new \Exception('Qualification or QualificationType must not be null');
    }
    if ($qualification instanceof Qualification && $qualificationType instanceof QualificationType) {
        throw new \Exception('Only one of Qualification or QualificationType can be present');
    }

    $sql = "
    SELECT *
    FROM staff_qualification AS sq ";
// Complicated SQL ensues that I've omitted from this SE question...

    if ($qualification instanceof Qualification) {
        $sql .= "AND q.id = :qualificationId ";
    } elseif ($qualificationType instanceof QualificationType) {
        $sql .= "AND q.qualification_type_id = :qualificationTypeId ";
    }

    $sql .= "
    GROUP BY sq.id
    ORDER BY sq.end_date
    ;
    ";

    $rsm = new ResultSetMapping();
    $rsm->addEntityResult(StaffQualification::class, 'sq');
    $rsm->addJoinedEntityResult(Staff::class, 's', 'sq', 'staff');
    $rsm->addFieldResult('sq', 'id', 'id');
    $rsm->addFieldResult('s', 'staff_id', 'id');
    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    if ($qualification instanceof Qualification) {
        $query->setParameter('qualificationId', $qualification->getId());
    } elseif ($qualificationType instanceof QualificationType) {
        $query->setParameter('qualificationTypeId', $qualificationType->getId());
    }
    $result = $query->getResult();
    return $result;
}

Answer 1

我认为最好的做法是提取字符串构建之外的逻辑并将其传递进来。您可以按照下面的内联方式或使用sprintf或Heredoc执行此操作。

/**
 * @param ?Qualification $qualification
 * @param ?QualificationType $qualificationType
 * @return StaffQualification[]
 * @throws Exception
 */
public function getStaffQualificationsExpiringOnQualificationOrQualificationType(
    ?Qualification $qualification,
    ?QualificationType $qualificationType
): array
{
    if ($qualification === null && $qualificationType === null) {
        throw new Exception('Qualification or QualificationType must not be null');
    }
    if ($qualification instanceof Qualification && $qualificationType instanceof QualificationType) {
        throw new Exception('Only one of Qualification or QualificationType can be present');
    }

    if ($qualification instanceof Qualification) {
        $filterSql = "q.id = :qualificationId ";
    } elseif ($qualificationType instanceof QualificationType) {
        $filterSql = "q.qualification_type_id = :qualificationTypeId ";
    }
    $sql = "
    SELECT *
    FROM staff_qualification AS sq
    WHERE 1=1
    AND $filterSql
    GROUP BY sq.id
    ORDER BY sq.end_date
    ;
    ";

    $rsm = new ResultSetMapping();
    $rsm->addEntityResult(StaffQualification::class, 'sq');
    $rsm->addJoinedEntityResult(Staff::class, 's', 'sq', 'staff');
    $rsm->addFieldResult('sq', 'id', 'id');
    $rsm->addFieldResult('s', 'staff_id', 'id');
    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    if ($qualification instanceof Qualification) {
        $query->setParameter('qualificationId', $qualification->getId());
    } elseif ($qualificationType instanceof QualificationType) {
        $query->setParameter('qualificationTypeId', $qualificationType->getId());
    }
    $result = $query->getResult();
    return $result;
}