ZF2 Tablegateway: Update dataset with self refering data

Question

I´m struggling with a quite simple SQL statement in ZF2 tablegateway. The SQL Statement should finally be:

UPDATE tbl SET sortOrder=sortOrder+1 WHERE parentId='1'

As you can see I simply want to incremt all sortOrder values by one for all datasets which belong to a parent with ID 1. Easy in SQL - hard in ZF2 :)

I tried the following (which could not work cause it expects a key=>value pair):

$this->tableGateway->update(array('sortOrder' => 'sortOrder + 1'), array('parentId' => $parentId));

I´ve also tried some other constructions but I can´t find a way how to solve this. Has anyone a hint for me? :)

Thanks a lot in advance, Michael

Answer 1

Try -

$this->tableGateway->update(array('sortOrder' => new \Zend\Db\Sql\Expression('sortOrder + 1')), array('parentId' => $parentId));

It will work fine.