PHP Phalcon. Use postgres nextval(sequence) in model

Question

I have a table in Postgres with DDL like this one:

CREATE TABLE robots(
    robot_id INTEGER NOT NULL CONSTRAINT robot_id_pkey PRIMARY KEY,
    name TEXT
);

I know I can insert a record with following SQL statement:

INSERT INTO robots (robot_id, name) VALUES (nextval('robots_seq'), 'WALL-E');

I need to make CRUD operations in Phalcon for this table. Also I want to use ORM features.
So I do:

$robot = new Robots();
$robot->setRobotId(new \Phalcon\Db\RawValue("nextval('robots_seq')"));
$robot->setName('WALL-E');
$robot->save();

And get the following exception:

Uncaught PDOException: SQLSTATE[22P02]: Invalid text representation:
7 ERROR: invalid input syntax for integer: 'nextval('robots_seq')';

Is there any way to accomplish this ?

Volkkerine · Accepted Answer · 2019-10-22 19:31:02Z

0

Phalcon assumes that your models with serial (auto increment) columns will have a sequence named [table_name]_[column_name]_seq.

In your case, in order to make Phalcon take care of handling auto increment columns you should have a sequence named robots_robot_id_seq instead of robots_seq for the column robot_id (which I'd call just "id", by the way).

This way you do not have to worry about setting the id when creating an object and after saving Phalcon will fill that field automatically for you:

CREATE TABLE robots(
robot_id SERIAL PRIMARY KEY NOT NULL,
name TEXT
);

$robot = new Robots();
$robot->setName('WALL-E');
$robot->save();
$robot->getRobotId(); // Should return last "nextval()" sequence

Hope it helps.

answered Oct 22, 2019 at 19:31

Volkkerine

864 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexey Chuhrov Over a year ago

Thanks. Unfortunately I can not change DDL structure of robots table, since I have a hot table for robots, let's call it robots_hot and in this table I'm using serial primary column and the sequence robots_seq for it. I insert rows in it with Phalcon ORM, it's all good; then, when some event arrives, I copy all rows from robots_hot to robots. But sometimes, I have to insert a row directly in robots bypassing robots_hot. That's the issue. Temporary solved it with using raw sql query.

Collectives™ on Stack Overflow

PHP Phalcon. Use postgres nextval(sequence) in model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related