Yii2 & PostgreSQL view - model does not fill its primary key field

[kkursor]

What steps will reproduce the problem?

I have a PostgreSQL table which is declared as

create table organizations_schema.organizations
(
    id                  serial                not null
        constraint organizations_pk
            primary key,
    ogrn                text,
    okpo                text,
    short_name          text                  not null,
    full_name           text,
    inn                 text,
    kpp                 text,
    okato               text,
    region              text,
    parent_organization integer
        constraint parent_organization_fk
            references organizations_schema.organizations
            on update cascade on delete cascade,
    industry_branch     smallint,
    okopf               text,
    okfs                text,
    okogvu              text,
    main_okved          text,
    yur_address         text,
    phones              text,
    faxes               text,
    oksm                text    default 643   not null,
    www                 text,
    emails              text,
    is_archive          boolean default false not null,
    is_opk              boolean default false not null,
);

and I also have a view which has (three latter) calculatable fields. To simplify, I changed their values to true (they are boolean).

create view organizations_view
            (id, ogrn, okpo, short_name, full_name, inn, kpp, okato, region, parent_organization, industry_branch,
             okopf, okfs, okogvu, main_okved, yur_address, phones, faxes, oksm, www, emails, is_archive, is_opk,
             is_developer, is_producer, is_vvst_manufacturer)
as
SELECT organizations.id,
       organizations.ogrn,
       organizations.okpo,
       organizations.short_name,
       organizations.full_name,
       organizations.inn,
       organizations.kpp,
       organizations.okato,
       organizations.region,
       organizations.parent_organization,
       organizations.industry_branch,
       organizations.okopf,
       organizations.okfs,
       organizations.okogvu,
       organizations.main_okved,
       organizations.yur_address,
       organizations.phones,
       organizations.faxes,
       organizations.oksm,
       organizations.www,
       organizations.emails,
       organizations.is_archive,
       organizations.is_opk,
       true AS is_developer,
       true AS is_producer,
       true AS is_vvst_manufacturer
FROM organizations_schema.organizations;

There is gii-generated ActiveRecord model associated with view (it was associated with table before and everything was ok).

The issue is when we create new model using actionCreate(), the model gets correctly written to database, but there is NULL value in id field and redirect is broken.

public function actionCreate()
    {
        $model = new Organizations();

        if ($model->load(Yii::$app->request->post())) {
            if ($model->save()) {
                // we get here, data is actually written to database but $model->id is null
                $model->id = Yii::$app->db->createCommand("SELECT currval('organizations_schema.organizations_id_seq'::regclass)")->queryScalar(); // this is temporary workaround to get id of last inserted value

                $this->addCreateLog($model);
            //    return $this->redirect(['view', 'id' => $model->id]);
                return $this->redirect('view?id=' . $model->id); // without workaround it redirects to view?id= instead of view?id=12345
            }
        }

        return $this->render(
            'create',
            ['model' => $model]
        );

    }//end actionCreate()

I've tried to replace tableName() from view to table and it works.

PostgreSQL allows updatable views and returns correct id after executing query like INSERT INTO organizations_schema.organizations_view(short_name, oksm, okpo) VALUES('test', 643, 12345678) RETURNING id

What is the expected result?

$model has filled primary key (id) attribute after saving data to database.

What do you get instead?

$model->id is null.

Additional info

Q	A
Yii version	2.0.37
PHP version	7.0
Operating system	Astra Linux 1.6

[samdark]

Any idea on how to fix it?

[kkursor]

probably add RETURNING {$this->primaryKey()} to insert query. But this probably won't work on composite PK.

[darkdef]

Maybe we will use: RETURNING * Also output inserted.* on mssql

[kkursor]

Maybe we will use: RETURNING * Also output inserted.* on mssql

а фто, так можно было? век живи - век учись)))

yeah, that's probably a solution. I'll check tomorrow

[darkdef]

@kkursor https://postgrespro.ru/docs/postgresql/9.5/dml-returning Need try this operation (returning *) with table contain triggers

[kkursor]

I've already read this. I knew about RETURNING field, but never noticed that one can use RETURNING *. That's probably a solution - after INSERT you may fill model object with fields that were returned by RETURNING as they are actually inserted into DB

Triggers - you mean INSTEAD OF?

[darkdef]

@kkursor May be troubles on table with trigger, also as here #18221

[kkursor]

I've worked it around like this:

public function afterSave($insert, $changedAttributes)
    {
        parent::afterSave($insert, $changedAttributes); // TODO: Change the autogenerated stub

        if (true === $insert) {
            $this->id = Yii::$app->db->createCommand("SELECT currval('organizations_schema.organizations_id_seq'::regclass)")->queryScalar();
        }
    }

PostgreSQL currval() function returns latest value of sequence achieved in current session, so it's not a problem, I think. That's a dirty hack and a crutch, but it works.

[lluisclava]

I have the same issue with Mysql and MSSql views.

I my opinion the problem is in the implementation of Schema->insert method:

    /**
     * Executes the INSERT command, returning primary key values.
     * @param string $table the table that new rows will be inserted into.
     * @param array $columns the column data (name => value) to be inserted into the table.
     * @return array|false primary key values or false if the command fails
     * @since 2.0.4
     */
    public function insert($table, $columns)
    {
        $command = $this->db->createCommand()->insert($table, $columns);
        if (!$command->execute()) {
            return false;
        }
        $tableSchema = $this->getTableSchema($table);
        $result = [];
        foreach ($tableSchema->primaryKey as $name) {
            if ($tableSchema->columns[$name]->autoIncrement) {
                $result[$name] = $this->getLastInsertID($tableSchema->sequenceName);
                break;
            }

            $result[$name] = isset($columns[$name]) ? $columns[$name] : $tableSchema->columns[$name]->defaultValue;
        }

        return $result;
    }

It's expectet to get the primarykey from the table schema but what happends if it's a view? Here we need to check if it's a view and then get the correct data from the source table.

My current workaround is in afterSave() model method:

    /**
     * {@inheritdoc}
     */
    public function afterSave($insert, $changedAttributes)
    {
        if($insert){
            $this->id = Yii::$app->db->getLastInsertID();
        }
        return parent::afterSave($insert,$changedAttributes);
    }    

[kkursor]

Workaround seems to stop working. When I create an ActiveRecord in transaction, use my workaround to get id and try to modify and save model, I get weird save query. PostgreSQL log looks like:

2021-06-29 23:55:51.914 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: SET NAMES 'utf8' 2021-06-29 23:55:51.915 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: BEGIN 2021-06-29 23:55:51.918 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: выполнение pdo_stmt_00000001: INSERT INTO "cafe_schema"."v_cafes" ("name", "latitude", "longitude", "schedule", "social_networks", "id", "organization", "view_count") VALUES ($1, $2, $3, $4::jsonb, $5::jsonb, $6, $7, $8) 2021-06-29 23:55:51.918 MSK [13782] coffeego_web@coffeego ПОДРОБНОСТИ: параметры: $1 = 'rgrgrgr', $2 = '55.7536', $3 = '37.6224', $4 = '[{"day": 0, "end_at": "", "start_at": ""}, {"day": 1, "end_at": "", "start_at": ""}, {"day": 2, "end_at": "", "start_at": ""}, {"day": 3, "end_at": "", "start_at": ""}, {"day": 4, "end_at": "", "start_at": ""}, {"day": 5, "end_at": "", "start_at": ""}, {"day": 6, "end_at": "", "start_at": ""}]', $5 = '[]', $6 = NULL, $7 = NULL, $8 = NULL 2021-06-29 23:55:51.922 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: DEALLOCATE pdo_stmt_00000001 2021-06-29 23:55:51.923 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: выполнение pdo_stmt_00000002: SELECT currval('cafe_schema.cafes_static_id_seq'::regclass) 2021-06-29 23:55:51.923 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: DEALLOCATE pdo_stmt_00000002 2021-06-29 23:55:51.928 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: выполнение pdo_stmt_00000003: UPDATE "cafe_schema"."v_cafes" SET "id"=$1, "images"=$2::jsonb WHERE "id" IS NULL 2021-06-29 23:55:51.928 MSK [13782] coffeego_web@coffeego ПОДРОБНОСТИ: параметры: $1 = '149', $2 = '[{"image": "60db88d7e1887", "is_main": true, "priority": 1}]' 2021-06-29 23:55:51.928 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: DEALLOCATE pdo_stmt_00000003 2021-06-29 23:55:51.930 MSK [13782] coffeego_web@coffeego СООБЩЕНИЕ: оператор: ROLLBACK

Why does it put NULL in query?

[kkursor]

changed crutch like @lluisclava wrote.

    public function afterSave($insert, $changedAttributes)
    {
        if (true === $insert) {
            $id = Yii::$app->db->createCommand("SELECT currval('cafe_schema.cafes_static_id_seq'::regclass)")->queryScalar();
            $this->id = $id;
            $this->setOldAttribute("id", $id);
        }

        return parent::afterSave($insert, $changedAttributes);

Queries changed but images field (which is being populated and re-saved after initial save) do not get to tables, but when I run these queries by hand - they work ok.

2021-06-30 00:45:50.923 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: SET NAMES 'utf8'
2021-06-30 00:45:50.923 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: BEGIN
2021-06-30 00:45:50.927 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000001: INSERT INTO "cafe_schema"."v_cafes" ("name", "latitude", "longitude", "schedule", "social_networks", "id", "organization", "view_count") VALUES ($1, $2, $3, $4::jsonb, $5::jsonb, $6, $7, $8)
2021-06-30 00:45:50.927 MSK [18833] coffeego_web@coffeego ПОДРОБНОСТИ:  параметры: $1 = 'rgrgrgr', $2 = '55.7514', $3 = '37.6189', $4 = '[{"day": 0, "end_at": "", "start_at": ""}, {"day": 1, "end_at": "", "start_at": ""}, {"day": 2, "end_at": "", "start_at": ""}, {"day": 3, "end_at": "", "start_at": ""}, {"day": 4, "end_at": "", "start_at": ""}, {"day": 5, "end_at": "", "start_at": ""}, {"day": 6, "end_at": "", "start_at": ""}]', $5 = '[]', $6 = NULL, $7 = NULL, $8 = NULL
2021-06-30 00:45:50.930 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000001
2021-06-30 00:45:50.930 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000002: SELECT currval('cafe_schema.cafes_static_id_seq'::regclass)
2021-06-30 00:45:50.930 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000002
=> 2021-06-30 00:45:50.935 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000003: UPDATE "cafe_schema"."v_cafes" SET "images"=$1::jsonb WHERE "id"=$2
=> 2021-06-30 00:45:50.935 MSK [18833] coffeego_web@coffeego ПОДРОБНОСТИ:  параметры: $1 = '[{"image": "60db948ee34f6", "is_main": true, "priority": 1}]', $2 = '162'
2021-06-30 00:45:50.935 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000003
2021-06-30 00:45:50.938 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000004: SELECT EXISTS(SELECT * FROM "system_schema"."users" WHERE "system_schema"."users"."id"=$1)
2021-06-30 00:45:50.938 MSK [18833] coffeego_web@coffeego ПОДРОБНОСТИ:  параметры: $1 = '1'
2021-06-30 00:45:50.938 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000004
2021-06-30 00:45:50.940 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000005: SELECT EXISTS(SELECT * FROM "system_schema"."users" WHERE "system_schema"."users"."id"=$1)
2021-06-30 00:45:50.940 MSK [18833] coffeego_web@coffeego ПОДРОБНОСТИ:  параметры: $1 = '1'
2021-06-30 00:45:50.940 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000005
2021-06-30 00:45:50.941 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  выполнение pdo_stmt_00000006: INSERT INTO "cafe_schema"."reviews" ("user_id", "coffeeshop_id", "rating", "comment", "up_votes", "down_votes") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"
2021-06-30 00:45:50.941 MSK [18833] coffeego_web@coffeego ПОДРОБНОСТИ:  параметры: $1 = '1', $2 = '162', $3 = '3', $4 = 'grgrg', $5 = '0', $6 = '0'
2021-06-30 00:45:50.942 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: DEALLOCATE pdo_stmt_00000006
2021-06-30 00:45:50.942 MSK [18833] coffeego_web@coffeego СООБЩЕНИЕ:  оператор: COMMIT