We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Return primary key after create/save

Hi,

I'm using PostgreSQL and I have the follow table in database:

CREATE TABLE tabla
(
  "TablaId" uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT tabla_pkey PRIMARY KEY ("TablaId")
);

And Model:

class Tabla extends \Phalcon\Mvc\Model {

    private $TablaId;

    //getters and setters
    function getTablaId() {
        return $this->TablaId;
    }

    function setTablaId($TablaId) {
        $this->TablaId = $TablaId;
    }
}

When I save the model like:

$data = [];
$obj = new Tabla();
$obj->save($data);

The result is OK, the object save in database but when I make a print_r($obj); I can't see the uuid autogenerated value.

¿How can I get it? It works with autoincrement column in mysql and serial in postgresql, but with a uuid autogenerated primary key not work in postgresql

Thanks

1


18.8k

Try this:

print_r($obj->getTablaId());


1.4k
edited Sep '15

Thanks Marcos, but I know it.

Really the problem is after save:

$obj->save(); // The object is save in database, and postgresql autogenerate uuid value for TablaId column.

echo $obj.>getTablaId(); // is null, but I expected the auto-generated value



34.6k

This way:

$data = [];
$obj = new Tabla();
$obj->save($data);

$obj->refresh();

echo $obj->getTablaId();


1.4k

No, i get the error:

"The record cannot be refreshed because it does not exist or is deleted"

But the record is save and exist in database.



34.6k

What version of Phalcon are you using?



1.4k

2.0.7



529
edited Feb '16

@netoec84

Did you solved the problem?

Edit: I may be encountering similiar issue. What is your workstadion? What OS, 32/64 bits?

I have 32 bits on Linux Lubuntu.



1.4k
in reply to konrad-psc

Really not solved the problem. But I generate UUID with PHP and not with auto generate it in postgresql

I work with CentOS 7, 64 bits, postgresql 9.4

@netoec84

Did you solved the problem?

Edit: I may be encountering similiar issue. What is your workstadion? What OS, 32/64 bits?

I have 32 bits on Linux Lubuntu.



1.9k
edited Jan '17

This method works from within a model for postgres:


    protected $_settable = NULL;  // array of settable fields   

    public function pg_create($data)
    {
        $table = $this->getSource();

        $di = \Phalcon\DI::getDefault();
        $db = $di["db"];

        $fields = array();

        if ($this->_settable && is_array($this->_settable)) {
            $fields = $this->_settable;
        } else {
            $columns = $db->describeColumns($table);
            foreach ($columns as $column) {
                $fields[] = $column->getName();
            }
        }

        $d = array_intersect_key($data, array_flip($fields));

        $fieldNames = implode(',', array_keys($d));
        $fieldValues = array_values($d);

        $qs = str_repeat('?,', count($fieldValues) - 1) . '?';

        $sql = "INSERT INTO {$table} ({$fieldNames}) VALUES ({$qs}) RETURNING *";

        if (($result = $db->query($sql, $fieldValues)) === false) return false;
        if (($rows = $result->fetchAll(PDO::FETCH_ASSOC)) === false) return false;

        $this->assign($rows[0]);
        return true;
    }


5.5k

I came across the same issue. I suspect this behavior will occur on any non-sequential primary key. The lastInsertId() PDO method will not return anything for this type of primary key, and Postgres will complain if you try.

A workaround could be to use 'RETURNING' for these specific types of columns. Eg.

Given the table;

CREATE TABLE "my_table" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), "label" varchar(64) NOT NULL );

The insert statement;

INSERT INTO my_table VALUES(DEFAULT, 'some label value');

Would become;

INSERT INTO my_table VALUES(DEFAUT, 'some label value') RETURNING id;

A prepared statement could then be used to return this value;

$stmt->fetchColumn();