Laravel：编写合理的嵌套条件查询（代码精简）

Hey there, let's fix this query to be cleaner, safer, and more robust. The core issues we need to address are:

• Ensuring the query only runs when at least one of the key fields ($credential_no, $ssn, $passport_no, $birthdate) has a value
• Ditching messy nested conditions
• Avoiding accidental matches on NULL values

Step 1: Build Dynamic, Safe Conditions #

Instead of nesting a bunch of if statements, we'll collect valid conditions in an array and use parameterized queries to stay safe from SQL injection. Here's a streamlined implementation (using PDO for database interactions, since it's widely used and secure):

// Initialize storage for query conditions and parameters
$whereClauses = [];
$bindParams = [];

// Add condition for each non-empty field
if (!empty($credential_no)) {
    $whereClauses[] = 'credential_no = :credential_no';
    $bindParams[':credential_no'] = $credential_no;
}

if (!empty($ssn)) {
    $whereClauses[] = 'ssn = :ssn';
    $bindParams[':ssn'] = $ssn;
}

if (!empty($passport_no)) {
    $whereClauses[] = 'passport_no = :passport_no';
    $bindParams[':passport_no'] = $passport_no;
}

if (!empty($birthdate)) {
    // Ensure birthdate is in a valid date format for your database first!
    $whereClauses[] = 'birthdate = :birthdate';
    $bindParams[':birthdate'] = $birthdate;
}

// Only execute the query if we have at least one valid condition
if (!empty($whereClauses)) {
    // Assemble the final SQL
    $sql = "SELECT * FROM employees WHERE " . implode(' OR ', $whereClauses);
    
    // Execute with parameter binding
    $stmt = $pdo->prepare($sql);
    $stmt->execute($bindParams);
    
    // Fetch potential duplicate records
    $potentialDuplicates = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    // Handle the case where no fields were provided
    trigger_error("Cannot run duplicate check: at least one field (credential_no, ssn, passport_no, birthdate) must be non-empty", E_USER_WARNING);
}

Key Improvements Explained #

• No More Nested Chaos: By collecting conditions in an array, we eliminate hard-to-read nested if blocks. Adding a new field later is as simple as adding one more if check.
• Safe from SQL Injection: Parameterized queries (using :placeholder syntax) prevent malicious input from breaking your database or stealing data—this is non-negotiable for production code.
• Automatic NULL Avoidance: We only add conditions for fields that are non-empty, so we never end up with a clause like credential_no = NULL (which won't work anyway, since NULL requires IS NULL checks) or matching all records where a field is NULL.
• Guard Against Empty Queries: The final check for !empty($whereClauses) ensures we never run an invalid query like SELECT * FROM employees WHERE (which would throw a database error).

Optional Adjustment: Match Multiple Fields Together #

If you want to find duplicates that match all provided fields instead of any one of them, just change the implode separator from ' OR ' to ' AND '. For example:

$sql = "SELECT * FROM employees WHERE " . implode(' AND ', $whereClauses);

That's it—this code is clean, maintainable, and solves all the original problems!

内容的提问来源于stack exchange，提问作者Iván E. Sánchez