Primary keys (Sequence) not setting after save

[Sohorev]

My table 'users' have following structure (postgree):

CREATE TABLE users
(
  id serial NOT NULL,
  login character varying(255),
  CONSTRAINT users_pkey PRIMARY KEY (id)
) WITH (
  OIDS=FALSE
);

I am write this code

$row = new RowGateway('id', 'users', $this->getAdapter());
$row->login = 'new';
$row->save();

And I have an exeption: While processing primary key data, a known key id was not found in the data array
I try use Pdo_Pgsql and Pgsql driver
Please fix it

[Sohorev]

to solve the problem I wrote a custom class RowGateway with method save.
It is characterized by obtaining the value primaryKeyValue:

$primaryKeyValue = $this->sql->getAdapter()->getDriver()->getLastGeneratedValue($this->tableGateway->getSequenceName());

namespace QP\Db\RowGateway;

class RowGateway extends \Zend\Db\RowGateway\RowGateway
{
    /**
     *
     * @var \QP\Db\TableGateway\Table
     */
    protected $tableGateway;

    public function __construct($primaryKeyColumn, $tableGateway)
    {
        $this->tableGateway = $tableGateway;
        parent::__construct($primaryKeyColumn, $tableGateway->table, $tableGateway->adapter);
    }

    public function save()
    {
        $this->initialize();

        if ($this->rowExistsInDatabase()) {
            // UPDATE

            $data = $this->data;
            $where = [];
            $isPkModified = false;

            // primary key is always an array even if its a single column
            foreach ($this->primaryKeyColumn as $pkColumn) {
                $where[$pkColumn] = $this->primaryKeyData[$pkColumn];
                if ($data[$pkColumn] == $this->primaryKeyData[$pkColumn]) {
                    unset($data[$pkColumn]);
                } else {
                    $isPkModified = true;
                }
            }

            $statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
            $result = $statement->execute();
            $rowsAffected = $result->getAffectedRows();
            unset($statement, $result); // cleanup

            // If one or more primary keys are modified, we update the where clause
            if ($isPkModified) {
                foreach ($this->primaryKeyColumn as $pkColumn) {
                    if ($data[$pkColumn] != $this->primaryKeyData[$pkColumn]) {
                        $where[$pkColumn] = $data[$pkColumn];
                    }
                }
            }
        } else {
            // INSERT
            $insert = $this->sql->insert();
            $insert->values($this->data);

            $statement = $this->sql->prepareStatementForSqlObject($insert);

            $result = $statement->execute();

            $primaryKeyValue = $this->sql->getAdapter()->getDriver()->getLastGeneratedValue($this->tableGateway->getSequenceName());
            if ($primaryKeyValue && count($this->primaryKeyColumn) == 1) {
                $this->primaryKeyData = [$this->primaryKeyColumn[0] => $primaryKeyValue];
            } else {
                // make primary key data available so that $where can be complete
                $this->processPrimaryKeyData();
            }
            $rowsAffected = $result->getAffectedRows();
            unset($statement, $result); // cleanup

            $where = [];
            // primary key is always an array even if its a single column
            foreach ($this->primaryKeyColumn as $pkColumn) {
                $where[$pkColumn] = $this->primaryKeyData[$pkColumn];
            }
        }

        // refresh data
        $statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
        $result = $statement->execute();
        $rowData = $result->current();
        unset($statement, $result); // cleanup

        // make sure data and original data are in sync after save
        $this->populate($rowData, true);

        // return rows affected
        return $rowsAffected;
    }
}

[froschdesign]

You can use the TableGateway feature "SequenceFeature".

[froschdesign]

Related to #165

[alextech]

More related to #186 inspired by #172, and in part discussion from #177 because of serial in his create statement. It is currently a deficiency in the SequenceFeature which makes it not ready to be documented and publicized that I am trying to solve in free time.

[alextech]

Also appears SequenceFeature cannot be applied to RowGateway, only TableGateway it seems? I will have to add support for that too.

[froschdesign]

@alextech
Can you check this: Add the Sequence feature and the RowGateway feature to TableGateway? The FeatureSet is needed.

[alextech]

Trying to do that, not understanding how to apply TableGateway to insertion problem. RowGatewayFeature from what see in docs and the test I made (following https://docs.zendframework.com/zend-db/row-gateway/)

$tbl = new TableGateway('usersdev', $this->dbcon, [new RowGatewayFeature('id')]);
$rs = $tbl->select(['id' => '2']);

/** @var RowGateway $row */
$row = $rs->current();
$row->name = 'Masha';
$row->save();

do not have this problem because once select query returns data array, follow up update() call already filters out keys specified in first parameter (primary key column name).

I do not see how to apply table gateway nor additional features to RowGateway for new data. Another undocumented feature? Can't be because

1. RowGateway has no setters to its internal featureSet array which is not used for anything besides adding its own instance as a feature I guess for purpose of allowing magic calls
2. RowGateway creates its own Select object completely independently https://github.com/zendframework/zend-db/blob/master/src/RowGateway/AbstractRowGateway.php#L160, no call to any feature, not wanting table gateway.

[froschdesign]

@alextech

I do not see how to apply table gateway nor additional features to RowGateway for new data.

The idea was to add a FeatureSet with the sequence and the RowGateway feature to the TableGateway,

RowGateway creates its own Select object ... not wanting table gateway.

This is why I asked for a check.

Thanks for you test!

[alextech]

Welcome :)

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#87.