I need to delete rows in a table where a NOT IN and a IN condition are satisfied. The query will use php arrays containing a variable amount of data.

I have successfully tested a SELECT query directly on my database like this:

SELECT *
FROM pivot
WHERE (table_1_id, table_2_id) NOT IN ((5,2),(5,3),(1,1),(3,1),(3,2))
  AND table_2_id IN (1,2,3)
  AND is_active = 1

On this sample data:

I get the correct result:

Now I need to implement this sql in php (as a DELETE) query using $table1Ids and $table2Ids. I've tried calling array_unique() and implode() to inject the array values into the query, but the logic isn't right.

$delete = "
    DELETE FROM pivot
    WHERE (table_1_id, table_2_id) 
    NOT IN (
        " . implode(',', array_unique($table1Ids)) . ", 
        " . implode(',', array_unique($table2Ids)) . "
    )
    AND table_2_id IN (
        " . implode(',', array_unique($table2Ids)) . "
    )
    AND is_active = 1";
$this->pdo->exec($delete);

I'm not sure how to set up the arrays and deliver them to the query in the right format.

CodePudding user response：

1. "zip-flatten" your two equal-length arrays so that they can be used to craft the parenthetical "row constructor" syntax in your sql.
2. Isolate the unique values in the second array for the second condition in your WHERE clause.
3. Count the arrays, generate the placeholder strings, and insert these variables into your sql.
4. Merge your variables and write them as an array of parameters for the execute() function.

Code: (Demonstration with SELECT instead of DELETE)

$table1Ids = [5, 5, 1, 3, 3];
$table2Ids = [2, 3, 1, 1, 2]; // assumed to always have identical length as $table1Ids

$zipFlattened = [];
foreach ($table1Ids as $index => $zipFlattened[]) {
    $zipFlattened[] = $table2Ids[$index];
}
// $zipFlattened = [5, 2, 5, 3, 1, 1, 3, 1, 3, 2]

$uniqueIds2 = array_unique($table2Ids);

$placeholders1 = implode(',', array_fill(0, count($table1Ids), '(?,?)'));
$placeholders2 = implode(',', array_fill(0, count($uniqueIds2), '?'));

$sql = <<<SQL
DELETE
FROM pivot
WHERE (table_1_id, table_2_id) NOT IN ($placeholders1)
  AND table_2_id IN ($placeholders2)
  AND is_active = 1
SQL;
$stmt = $pdo->prepare($sql);
$stmt->execute(array_merge($zipFlattened, $uniqueIds2));

CodePudding user response：

use this query:

DELETE FROM pivot
    WHERE table_1_id NOT IN ( ".implode(',', array_unique($table1Ids)).")
    AND 
    table_2_id NOT IN ( ".implode(',', array_unique($table2Ids)).")
        ".implode(',', array_unique($table2Ids))."
    AND is_active = 1";