CODEWITHSUNDEEP
phppostgresqlzend-framework2
b4rt3kk
b4rt3kk

Reputation: 1569

ZEND2 - lastInsertValue returns NULL

My table looks like this:

CREATE TABLE data.brand_list
(
  id bigserial NOT NULL,
  brand_name text,
  created_at timestamp with time zone DEFAULT now(),
  ghost boolean,
  CONSTRAINT brand_list_id_pkey PRIMARY KEY (id)
)

It also has sequence:

 CREATE SEQUENCE data.brand_list_id_seq
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 649
 CACHE 1;
 ALTER TABLE data.brand_list_id_seq

My code in Zend model:

public function addRow(BrandList $brandList)
{
    $data = array(
        'id' => $brandList->id,
        'brand_name' => $brandList->brand_name,
        'created_at' => $brandList->created_at,
        'ghost' => $brandList->ghost,
    );

    $id = (int)$brandList->id;
    if ($id == 0) {
        unset($data['id']);
        $row = $this->tableGateway->insert($data);
        var_dump($this->tableGateway->getLastInsertValue());die;
        return $id;
    } else {
        if ($this->findOne($id)) {
            $this->tableGateway->update($data, array('id' => $id));
        } else {
            throw new \Exception('Form id does not exist');
        }
    }
}

And $row return int(1) always, $this->tableGateway->getLastInsertValue() returns NULL. What am I doing wrong? I've tried everything... With no luck.

Upvotes: 0

Views: 319

Answers (3)

dixromos98
dixromos98

Reputation: 754

You can try a raw query just to see if your getting the result you want.

  $adapter = $this->tableGateway->getAdapter();

$rowset = "SELECT MAX(ID)...";

$resultset = $adapter->query($rowset,array());

$rowData = $resultset->toArray();
return $rowData;

OR

use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Select;
Use Zend\Db\Sql\Expression;    
$adapter = $this->tableGateway->getAdapter();
    $sql = new Sql($adapter);
    $select = $sql->select();
    $select->from('table_name')->columns(array('id' => new Expression('max(id)') ));
    $selectString = $sql->getSqlStringForSqlObject($select);
    $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
    $resultSet = new ResultSet();
    $resultSet->initialize($results);
    return $resultSet->toArray();

Upvotes: 1

Ruwantha
Ruwantha

Reputation: 2653

Try $this->tableGateway->lastInsertValue;
instead of $this->tableGateway->lastInsertValue();

Upvotes: 0

b4rt3kk
b4rt3kk

Reputation: 1569

Well, I've found maybe not too elegant solution, but it works for me. What I've found - Zend 2 have problems with postgres sequences. This is my solution:

$sql = "SELECT nextval('data.brand_list_id_seq') nextval";
        $resultSet = $this->tableGateway->getAdapter()->getDriver()->getConnection()->execute($sql);
        foreach ($resultSet as $item) {
            $data['id'] = $item['nextval'];
        }
        $this->tableGateway->insert($data);

        return $data['id'];

You should apply code above to your model insert method.

Upvotes: 0

Related Questions