Zendframework 2 postgresql update with "not"

Question

Is is possible to pass to database the following sql query using tableGateway, if so, how would such a command look like ?

UPDATE table_data SET active = not active where table_data.id = 12;

Answer 1

You need to use a Zend\Db\Sql\Expression, this class tells Zend\Db that you know what you're doing and that the content of the string passed to this class shouldn't be transformed, but rather used as is.

// build the table gateway object
$adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
$tableIdentifier = new TableIdentifier('table_data', 'public');
$tableGateway = new TableGateway($tableIdentifier, $adapter);

// create the filter
$where = new \Zend\Db\Sql\Where();
$where->equalTo('id', '12');

// update table
$tableGateway->update(
    ['active' => new \Zend\Db\Sql\Expression('not active')], 
    $where
);