MySQL: How do you query on a compound-primary-key? Specifically, a NOT IN query?

Question

I have a comment table and a comment_edit table, as well as olddb_edit. Simplified, the relevant table looks like this:

CREATE TABLE `olddb_edit` (
    edit_id INT NOT NULL,
    edit_time INT NOT NULL,
    edit_text TEXT NOT NULL,

    PRIMARY KEY (edit_id, edit_time)

) ENGINE=InnoDB;

And now I want to migrate content from another DB into the edit-table, but skip some table-rows like test-comments. I'm using CakePHP for this (Phinx, actually).

Usually, this suffices:

$skippable = array(
    12345, 23456, 34567, 45678,
);
$rows = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_comment')
    ->where(array(
        'comment_id NOT IN' => $skippable,
    ))
    ->execute()
    ->fetchAll('assoc')
;

But a simple NOT IN clause obviously doesn't work with compound-primary-keys.

I was thinking, the $skippable array should look like this:

$skippable = array(
    array('id' => 707969,   'time' => 1434462225),
    array('id' => 707969,   'time' => 1434462463),
    array('id' => 707969,   'time' => 1434462551),
);

And then I'll run the where clause through a for-loop or something. But to be honest, I don't even know how to do it in vanilla-MySQL.

There might be a solution posted on SO already, but I couldn't find any (other than ones specific to other applications). The algorithm is not my friend, I guess.

Answer 1

Never mind, I figured it out myself while formulating the question. I'll post the answer anyway, for others with a similar question.

---

First, vanilla-MySQL. It's as intuitive as you would think if you break up the NOT IN (imo):

SELECT * FROM olddb_edit WHERE
NOT (
    (edit_id = 707969 AND edit_time = 1434461454)
OR  (edit_id = 707969 AND edit_time = 1434461503)
OR  (edit_id = 707969 AND edit_time = 1434461925)
);

---

And with the CakePHP / Phinx query-builder, you use an anonymous function, a for-loop and a not-or construct:

$qb = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_edit')
    ->where(array(
        'edit_some_other_optional_condition = 1',
    ))

    // also skip skippables.
    ->where(function($exp) use ($skippable) {
        $ORed = array();
        foreach ($skippable as $edit) {
            array_push($ORed, array(
                'edit_id'   => $edit['id'],
                'edit_time' => $edit['time'],
            ));
        }
        return $exp->not($exp->or_($ORed));
    })
;

---

UPDATE: Based on @ndm's comment, I present a satisfying solution using TupleComparison. — (@ndm, feel free to post your answer still if you want to. I will delete/edit mine and choose yours. You deserve the credit).

// remove the keys from my previous solution.
$skippable = array(
    array(707969,   1434462225),
    array(707969,   1434462463),
    array(707969,   1434462551),
);

$qb = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_edit')
    ->where(array(
        'edit_some_other_optional_condition = 1',
    ))

    // also skip skippables.
    ->where(new Cake\Database\Expression\TupleComparison(
        array('edit_id', 'edit_time'),
        $skippable,
        array('integer', 'integer'),
        'NOT IN'
    ))
;