Reset INSERT queryBuilder for re-use, in Phinx

Question

I'm having a particular problem with Phinx (version 0.10.8): I have to migrate one table as such that that row is inserted one table, and then the AUTO_INCREMENTed last-insert-ID is inserted in another table.

Since I am in a for-loop, I'd just like to keep recycling the same insert-query-builder for the insert in the first table; rather than rebuilding the entire insert-builder. But I don't know how to reset the VALUES data.

An example to illustrate the problem:

// insert-builder I hope to re-use.
$builder = $this->getQueryBuilder()->insert(array(
    'note',
))->into('test_table');

// cache this empty state for reset attempt #2.
$empty = $builder;

// insert one row of values.
$builder->values(array(
    'note' => "Please don't copy me. Please don't copy me. Please don't copy me ...",
))->execute();

// dump info.
var_dump($this->getAdapter()->getConnection()->lastInsertId());
$rows = $this->fetchAll("SELECT COUNT(*) FROM test_table");
var_dump($rows);

// reset attempts.    
//$builder->getValueBinder()->reset();              // (1)
$builder = $empty;                                  // (2)
//$builder->getQuery()->getValueBinder()->reset();  // (3)

// insert second row.
$builder->values(array(
    'note' => "Second insert.",
))->execute();

// dump info.
var_dump($this->getAdapter()->getConnection()->lastInsertId());
$rows = $this->fetchAll("SELECT COUNT(*) FROM test_table");
var_dump($rows);

Number (3) gives me an exception and (1) and (2) give me this same output, which is that I have 3 rows after 2 inserts:

string(1) "1"
array(1) {
  [0]=>
  array(2) {
    ["COUNT(*)"]=>
    string(1) "1"
    [0]=>
    string(1) "1"
  }
}
string(1) "2"
array(1) {
  [0]=>
  array(2) {
    ["COUNT(*)"]=>
    string(1) "3"
    [0]=>
    string(1) "3"
  }
}

I was fishing in the dark, anyway. I can't really find any good documentation on this.

/vendor/cakephp/database/ValueBinder.php does seem to have a public reset method. But I'm not sure how to get to that ValueBinder.

---

This thread suggests to use closures, which is actually a good idea now that I think about it. They were mentioned in passing in this doc. But how it work? I dumb.

// not like this.
$values = array(
    'note' => "Please don't copy me. Please don't copy me. Please don't copy me ...",
);
$this->execute(function() use ($builder, $values) {
    return $builder->values($values)->execute();
});

// not like this.
$this->execute(function($builder) use ($values) {
    return $builder->values($values)->sql();
});

// not like this.
$builder->values(function($builder) use ($values) {
    return $builder->values($values);
})->execute();

Answer 1

Reusing queris can be tricky, and I'm not sure if it's really advised. Anyways, by default values are being added, not replaced, this allows to easily build multi row insert queries dynamically, so there's your problem.

If you want to replace the values, then you need to access the underlying \Cake\Database\Expression\ValuesExpression object, which can be obtained via the query builder's clause() method. And while you could use ValuesExpression::setValues() to override the existing values with new ones (this would need to be a nested array as this method accepts multi-row data), doing so would not put the query object back into a dirty state, and would also not clear the value binder, so your best bet would probably be to use ValuesExpression::setValues() to reset the data, and then use the query builders values() method to set the new data, something like:

$stmt = $builder
    ->values([
        'note' => 'first note'
    ])
    ->execute();
// ...
$stmt->closeCursor();

// reset the values clause
$builder
    ->clause('values')
    ->setValues([]);

// define new values, this will put the query in a dirty state and reset the value binder
$stmt = $builder
    ->values([
        'note' => 'second note'
    ])
    ->execute();
// ...
$stmt->closeCursor();