ZF2 How to get last insert id value?

Question

I am stuck with getting last insert id with Zend framework 2 and I gave up on this...

There are tried combinations:

var_dump($this->tableGateway->insert($insert));
var_dump($this->tableGateway->lastInsertValue);
var_dump($this->tableGateway->getLastInsertValue());
var_dump($this->tableGateway->getAdapter()->getDriver()->getConnection()->getLastGeneratedValue());

Value is inserting to table, but every line (except first, which gives int "1") returns null. Please don't tell me, that such a big framework does not give possibility to get last insert id value!?

Answer 1

This does not work with oracle oci8 because it is not implemented yet. See here: https://github.com/zendframework/zf2/blob/master/library/Zend/Db/Adapter/Driver/Oci8/Connection.php#L343

Answer 2

In App\Model\AlbumTable which extends AbstractTableGateway I get last id by $inserted_id = $this->lastInsertValue;

as an example:

 if ($id == 0) {
            $this->insert($data);
            $inserted_id = $this->lastInsertValue;    
        } elseif ($this->getAlbum($id)) {
            $this->update(
                $data, array(
                'id' => $id,
                )
            );
        } else {
            throw new \Exception('Form id does not exist');
        }

Answer 3

There is another possibility to add FeatureSet to TableGateway.

In Module.php you can define (table User for example)

            'UserTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                $resultSetPrototype = new ResultSet();
                $resultSetPrototype->setArrayObjectPrototype(new UserEntity());
                return new TableGateway('user', $dbAdapter, new Feature\SequenceFeature('user','user_id_seq'), $resultSetPrototype);

But there is a bug in SequenceFeature. Everything is ok, when you use public schema. When you have to use other schema you should use Zend\Db\Sql\TableIdentifier;

In this case Module.php should be:

            'UserTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                $resultSetPrototype = new ResultSet();
                $resultSetPrototype->setArrayObjectPrototype(new UserEntity());
                return new TableGateway(new TableIdentifier('user','someSchema'), $dbAdapter, new Feature\SequenceFeature(new TableIdentifier('user','someSchema'),'user_id_seq'), $resultSetPrototype);

In this case will be an error with Postgresql query SELECT NEXTVAL, because Feature\SequenceFeature will use public schema instead of defined in first argument to prepare query

SELECT NEXTVAL('user_id_seq')

In this case sql query should be

SELECT NEXTVAL('someSchema'.'user_id_seq')

Answer 4

I know this was a while back, but after spending a ton of time on this particular problem I wanted to post the solution for future Googlers experiencing the same problem.

The issue is the Pgsql driver requires a name of the sequence to return the last id like this:

$adapter->getDriver()->getLastGeneratedValue("sequence_name");

This works in 2.3.1. There was a bug in 2.2.2 (and possibly later) where name was not a param of the getLastGeneratedValue in the driver, so you'd have to call the connection directly.

$adapter->getDriver()->getConnection()->getLastGeneratedValue

So that's what I found by looking through the code. Best solution is to make sure you have ZF2 updated, and use

$adapter->getDriver()->getLastGeneratedValue("sequence_name");

Answer 5

Ok, I know the question is few month old, though it may be useful for those who are searching for a solution to this problem to get the last insert values the best way is to get the value from the adapter interface

do your insert //then
$id = $adapter->getDriver()->getLastGeneratedValue();

This worked well for me for mysql database

Answer 6

In postgres you need setup SequenceFeature:

...
use Zend\Db\TableGateway\Feature;
...
public function setDbAdapter( Adapter $adapter ) {
   ...
   $this->featureSet = new Feature\FeatureSet();
   $this->featureSet->addFeature(new Feature\SequenceFeature('YOUR_FIELD_NAME','YOUR_SEQUENCE_NAME'));
   $this->initialize();
}

Now $this->tableGateway->getLastInsertValue(); should work.