Problem with updating table view

This looks like it should work. Can you paste what the INSERT statement looks like? Could you also paste what the SELECT statement looks like? That might give us a clue.

To post your queries, please use code blocks. See how: https://forum.phalcon.io/help/markdown

This is my code

<?php

$object = SmEventModel::findFirst("EventNr = 19159");
$object->EventName = "test";
$object->update();

I paste the INSERT and SELECT.

When I used update() it always try to INSERT.

[2020-10-14T18:01:19+02:00][info] SELECT sm_event.BranchNr, sm_event.EventNr, sm_event.EventName, sm_event.EventDescription, sm_event.Eventcolor, sm_event.Eventfromdate, sm_event.Eventtodate, sm_event.CalendarNr, sm_event.ParentEventNr, sm_event.UserNr, sm_event.framecolnr, sm_event.backcolnr, sm_event.frametypenr, sm_event.timestamp, sm_event.status, sm_event.foreignlink, sm_event.foreignlinktype, sm_event.exclusive, sm_event.blocked, sm_event.templateId FROM sm_event WHERE sm_event.EventNr = 19159 LIMIT :APL0

[2020-10-14T18:01:19+02:00][info] INSERT INTO sm_event (BranchNr, EventNr, EventName, EventDescription, Eventcolor, Eventfromdate, Eventtodate, CalendarNr, ParentEventNr, UserNr, framecolnr, backcolnr, frametypenr, timestamp, status, foreignlink, foreignlinktype, exclusive, blocked, templateId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This is my table view

<?php

public function initialize()
    {
        $this->setSource("sm_event");
        $this->hasMany('EventNr', 'App\Models\SmEventitems', 'EventNr', ['alias' => 'smEventitems']);
    }

I want to update and he always try insert and its the reason why I get the error

"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '19159' for key 'PRIMARY'"

Is EventNr the primary key? Is it specified in the database as being the primary key column of the table?

Can you paste the result of calling desc sm_event? Pasting it like code, but using "sql" instead of "php" will keep the formatting.

Yes EventNr is primary key of the main table. He want to insert the same column to the table not updated It happens only on the table view..

I'm aware - I'm asking for the table definition to try to figure out why Phalcon is INSERT-ing instead of UPDATE-ing. That may be due to how the table is defined.

DESCRIBE sm_event

BranchNr int(11) NO 1

EventNr int(11) NO 0

EventName varchar(255) NO NULL

EventDescription longtext YES NULL

Eventcolor varchar(15) NO 3300ff

Eventfromdate timestamp NO current_timestamp()

Eventtodate timestamp NO current_timestamp()

CalendarNr int(11) NO 1

ParentEventNr int(11) NO 0

UserNr int(11) NO 0

framecolnr int(11) NO 0

backcolnr int(11) NO 0

frametypenr int(11) NO 0

timestamp datetime NO NULL

status int(11) NO -1

foreignlink varchar(32) YES NULL

foreignlinktype int(11) NO 0

exclusive tinyint(4) NO 0

blocked tinyint(4) NO 0

templateId int(11) YES NULL

Create view

<?php

public function up()
    {
        $definition = array('sql' => 'select `event`.`filialnr` AS `BranchNr`,
                `event`.`nlfdevent` AS `EventNr`,
                `event`.`seventbez` AS `EventName`,
                `event`.`seventbeschreibung` AS `EventDescription`,
                `event`.`scolor` AS `Eventcolor`,
                `event`.`dtvon` AS `Eventfromdate`,
                `event`.`dtbis` AS `Eventtodate`,
                `event`.`calendid` AS `CalendarNr`,
                `event`.`parnlfdevent` AS `ParentEventNr`,
                `event`.`usernr` AS `UserNr`,
                `event`.`framecolnr` AS `framecolnr`,
                `event`.`backcolnr` AS `backcolnr`,
                `event`.`frametypenr` AS `frametypenr`,
                `event`.`timestamp` AS `timestamp`,
                `event`.`status` AS `status`,
                `event`.`foreignlink` AS `foreignlink`,
                `event`.`foreignlinktype` AS `foreignlinktype`,
                `event`.`exklusiv` AS `exclusive`,
                `event`.`blockiert` AS `blocked`,
                `event`.`templateId` AS `templateId` 
                from `event`');

        self::$connection->dropView("sm_event");
        self::$connection->createView("sm_event", $definition);
    }

That doesn't look like EventNr is defined as the primary key, though your output looks entirely different from what I get when I describe a table. Are you using MySQL/MariaDB? Your output should look something like:

+-------------------+-------------------------------+------+-----+---------+----------------+
| Field             | Type                          | Null | Key | Default | Extra          |
+-------------------+-------------------------------+------+-----+---------+----------------+
| id                | int(10) unsigned              | NO   | PRI | NULL    | auto_increment |
| prev_upload_id    | int(10) unsigned              | YES  | MUL | 0       |                |
| new_upload_id     | int(10) unsigned              | NO   | MUL | 0       |                |
| prev_applicant_id | int(10) unsigned              | YES  | MUL | 0       |                |
| new_applicant_id  | int(10) unsigned              | NO   | MUL | 0       |                |
| status            | enum('new','done','progress') | NO   |     | new     |                |
| field             | varchar(255)                  | NO   |     | 0       |                |
| prev_value        | varchar(255)                  | YES  |     | 0       |                |
| new_value         | varchar(255)                  | YES  |     | 0       |                |
+-------------------+-------------------------------+------+-----+---------+----------------+

DESCRIBE event
+-------------------+-------------------------------+------+-----+----------------------+----------------+
| Field             | Type                          | Null | Key | Default              | Extra          |
+-------------------+-------------------------------+------+-----+----------------------+----------------+
| filialnr          | int(11)                       | NO   | MUL | 1                    |                |
| nlfdevent         | int(11)                       | NO   | PRI | 0                    | auto_increment |
| seventbez         | varchar(255)                  | NO   |     | NULL                 |                |
| seventbeschreibung| longtext                      | YES  |     | NULL                 |                |
| scolor            | varchar(15)                   | NO   |     | 3300ff               |                |
| dtvon             | timestamp                     | NO   | MUL | current_timestamp()  |                |
| dtbis             | timestamp                     | NO   | MUL | current_timestamp()  |                |
| calendid          | int(11)                       | NO   |     | 1                    |                |
| parnlfdevent      | int(11)                       | NO   |     | 0                    |                |
| usernr            | int(11)                       | NO   |     | 0                    |                |
| framecolnr        | int(11)                       | NO   |     | 0                    |                |
| backcolnr         | int(11)                       | NO   |     | 0                    |                |
| frametypenr       | int(11)                       | NO   |     | 0                    |                |
| timestamp         | datetime                      | NO   |     | NULL                 |                |
| status            | int(11)                       | NO   |     | -1                   |                |
| foreignlink       | varchar(32)                   | YES  |     | NULL                 |                |
| foreignlinktype   | int(11)                       | NO   |     | 0                    |                |
| exklusiv          | tinyint(4)                    | NO   |     | 0                    |                |
| blockiert         | tinyint(4)                    | NO   |     | 0                    |                |
| templateId        | int(11)                       | YES  |     | NULL                 |                |
+-------------------+-------------------------------+------+-----+---------+-----------------------------+

DESCRIBE sm_event it is view of the event table

+-------------------+-------------------------------+------+-----+----------------------+----------------+
| Field             | Type                          | Null | Key | Default              | Extra          |
+-------------------+-------------------------------+------+-----+----------------------+----------------+
| BranchNr          | int(11)                       | NO   |     | 1                    |                |
| EventNr           | int(11)                       | NO   |     | 0                    |                |
| EventName         | varchar(255)                  | NO   |     | NULL                 |                |
| EventDescription  | longtext                      | YES  |     | NULL                 |                |
| Eventcolor        | varchar(15)                   | NO   |     | 3300ff               |                |
| Eventfromdate     | timestamp                     | NO   |     | current_timestamp()  |                |
| Eventtodate       | timestamp                     | NO   |     | current_timestamp()  |                |
| CalendarNr        | int(11)                       | NO   |     | 1                    |                |
| ParentEventNr     | int(11)                       | NO   |     | 0                    |                |
| UserNr            | int(11)                       | NO   |     | 0                    |                |
| framecolnr        | int(11)                       | NO   |     | 0                    |                |  
| backcolnr         | int(11)                       | NO   |     | 0                    |                |
| frametypenr       | int(11)                       | NO   |     | 0                    |                |
| timestamp         | datetime                      | NO   |     | NULL                 |                |
| status            | int(11)                       | NO   |     | -1                   |                |
| foreignlink       | varchar(32)                   | YES  |     | NULL                 |                |
| foreignlinktype   | int(11)                       | NO   |     | 0                    |                |
| exclusive         | tinyint(4)                    | NO   |     | 0                    |                |
| blocked           | tinyint(4)                    | NO   |     | 0                    |                |
| templateId        | int(11)                       | YES  |     | NULL                 |                |
+-------------------+-------------------------------+------+-----+---------+-----------------------------+  

When I used updated() on event table its work perfect he try to UPDATE but when I used updated() on table view he try to INSERT.

I think that you cannot create a primary key on a view but if your view is based on a table with a primary key then the primary key will be reflected in the view also.

Thank you very much for your time.

Ok, so here we see that EventNr is not set as the primary key of the table - I believe that's what's causing your problem. When Phalcon tries to update(), it first looks for a record with a matching primary key. If one is not found, it does an insert because there's nothing to update. If you set EventNr as the primary key (which assumes there will only be one row per EventNr), then Phalcon will be able to update the row.

However, your original error: "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry for key 'PRIMARY'" can't be caused by trying to insert into sm_event because, like I said, has no primary key. It must be a different query causing the error.

Ok thank you.

I have another question. How to set primary key in table view because its not base table. I using MySQL/MariaDB. Thank you.

I think if you set it in the table, it should be carried through to the view. I'm not sure though. Personally I've never used views - I've never seen the need for them.

DESCRIBE event
+-------------------+-------------------------------+------+-----+----------------------+----------------+
| Field             | Type                          | Null | Key | Default              | Extra          |
+-------------------+-------------------------------+------+-----+----------------------+----------------+
| filialnr          | int(11)                       | NO   | MUL | 1                    |                |
| nlfdevent         | int(11)                       | NO   | PRI | 0                    | auto_increment |
| seventbez         | varchar(255)                  | NO   |     | NULL                 |                |
| seventbeschreibung| longtext                      | YES  |     | NULL                 |                |
| scolor            | varchar(15)                   | NO   |     | 3300ff               |                |
| dtvon             | timestamp                     | NO   | MUL | current_timestamp()  |                |
| dtbis             | timestamp                     | NO   | MUL | current_timestamp()  |                |
| calendid          | int(11)                       | NO   |     | 1                    |                |
| parnlfdevent      | int(11)                       | NO   |     | 0                    |                |
| usernr            | int(11)                       | NO   |     | 0                    |                |
| framecolnr        | int(11)                       | NO   |     | 0                    |                |
| backcolnr         | int(11)                       | NO   |     | 0                    |                |
| frametypenr       | int(11)                       | NO   |     | 0                    |                |
| timestamp         | datetime                      | NO   |     | NULL                 |                |
| status            | int(11)                       | NO   |     | -1                   |                |
| foreignlink       | varchar(32)                   | YES  |     | NULL                 |                |
| foreignlinktype   | int(11)                       | NO   |     | 0                    |                |
| exklusiv          | tinyint(4)                    | NO   |     | 0                    |                |
| blockiert         | tinyint(4)                    | NO   |     | 0                    |                |
| templateId        | int(11)                       | YES  |     | NULL                 |                |
+-------------------+-------------------------------+------+-----+---------+-----------------------------+

This is my base table and the primary key is set!

I create the view normal way. You cannot create a primary key on a view but if your view is based on a table with a primary key then the primary key will be reflected in the view also.

I think that in phalcon framework cannot update the table view or I mistake?

I'm guessing you can't update a view. From what I've seen, a view is basically just the result of a SELECT statement - it doesn't actually hold the data. If you want to update, do it on the table itself.

In case there's any confusion, you don't need to use a table view with Phalcon - it and it's ORM work just fine working directly on the tables.

When the view is 1:1 to base table and when you update the view the automatically update the base table or when you insert the data to the view aumaticaly insert to the base table. You can try it. Update view is normal mysql command.

I need work with table view because when you see the colums are diferent and for the language and project I need work with table view.

I must find some solution maybe I use mapping the column or use the db execute.

Thank you for your time.