No update/save with foreign key

Hi !

I have 2 tables Users and UserStatus, with models generated with phalcon tools, so the relationships hasMany/belongsTo are correct.

When I try to update the status of the user, there is no errors, and no change in the db.

$user = Users::findFirst(16);
$user->user_status_id = 1;
$user->save(); // or update()

I tried this post : https://forum.phalcon.io/discussion/3210/not-update-foreignkey-value But no changes.

And I found this issue : https://github.com/phalcon/cphalcon/issues/1867 That is exactly my problem.

I have Phalcon 2.0.7, PHP 5.6. I know the issue is pending, but I want to know if you have a solution ?, because I tried all the answers in the post and nothing works for me...

By the way, after logging the SQL, this is the output (for the 3 lines above) :

UPDATE `users` SET `username` = ?, `email` = ?, `password` = ? WHERE `id` = ? AND `user_status_id` = ?

As you can see, the modified field is put in WHERE clause, and not in the fields to be modified. So as the row doesn't exists n the db, no change and no error (because the sql generated in right).

Thank you for your help or answers !



122
edited Dec '15

I use Phalcon 2.0.9 and I have this same problem. I have 2 foreign keys and when i try to update it, i become no errors and my SQL query is like yours:

UPDATE `users` SET `name` = 'testUSER', `email` = '[email protected]', `password` = 'FGbAaj8etqTu0HIp3DSGd3bOOLqNHUW', `mustChangePassword` = 'N', `notyficationId` = '0', `banned` = 'Y', `suspended` = 'Y', `active` = 'Y' WHERE `id` = '31' AND `profilesId` = 3 AND `companyId` = '3'

Here my PHP code:

...
$user = Users::findFirst($id);
...
$user->assign(array(
    'name' => $this->request->getPost('name', 'striptags'),
    'email' => $this->request->getPost('email', 'email'),
    'profilesId' => $this->request->getPost('profilesId', 'int'),
    'companyId' => $this->request->getPost('companyId', 'int'),
    'banned' => $this->request->getPost('banned'),
    'suspended' => $this->request->getPost('suspended'),
    'active' => $this->request->getPost('active'),
    'password' => $this->security->hash($this->request->getPost('password'))
));
if (!$user->update()) {
...

profilesId and companyId are the foreign keys and we can see that they moved to the WHERE clause. @dschissler: i try $user->update(0 but without success....

I would be grateful for a solution or workaround, Cheers Damian

=================

EDIT:

Today I had more time so I decided to test a few things. I remove the 2 foreign keys from my schema the and after this the update/save behavior works correctly, it was tested on version 2.0.8 and 2.0.9, could it be a BUG?

Here my old SQL schema (with foreign keys):

CREATE TABLE IF NOT EXISTS `blob`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` CHAR(60) NOT NULL,
  `mustChangePassword` CHAR(1) NULL DEFAULT NULL,
  `profilesId` INT(10) UNSIGNED NOT NULL,
  `companyId` INT(10) UNSIGNED NOT NULL,
  `notyficationId` INT(10) NOT NULL,
  `banned` CHAR(1) NOT NULL,
  `suspended` CHAR(1) NOT NULL,
  `active` CHAR(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `profilesId`, `companyId`),
  INDEX `profilesId` (`profilesId` ASC),
  INDEX `companyId` (`companyId` ASC),
  CONSTRAINT `users_profiles`
    FOREIGN KEY (`profilesId`)
    REFERENCES `blob`.`profiles` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `users_company`
    FOREIGN KEY (`companyId`)
    REFERENCES `blob`.`company` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = utf8;

and here my new one (without foreign keys)

CREATE TABLE IF NOT EXISTS `blob`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` CHAR(60) NOT NULL,
  `mustChangePassword` CHAR(1) NULL DEFAULT NULL,
  `profilesId` INT(10) UNSIGNED NOT NULL,
  `companyId` INT(10) UNSIGNED NOT NULL,
  `notyficationId` INT(10) NOT NULL,
  `banned` CHAR(1) NOT NULL,
  `suspended` CHAR(1) NOT NULL,
  `active` CHAR(1) NULL DEFAULT NULL,
  PRIMARY KEY (`profilesId`, `companyId`),
  INDEX `profilesId` (`profilesId` ASC),
  INDEX `companyId` (`companyId` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = utf8;

after these changes the query is again correct

UPDATE `users` SET `name` = 'testUSER', `email` = '[email protected]', `password` = 'FGbAaj8etqTu0HIp3DSGd3bOOLqNHUW', `mustChangePassword` = 'N', `profilesId` = '2', `companyId` = '5', `notyficationId` = '0', `banned` = 'Y', `suspended` = 'Y', `active` = 'Y' WHERE `id` = '31


122
edited Dec '15

here my Users model

If the two of you could post your entire model PHP code then it might reveal some issues. In particular how you are setting up the relationships.



122
edited Dec '15

@dschissler, i remove the option arguments but nothing special happened - the query is still wrong, my next step was to remove the 2 hasOne relationship but again nothing happens, because i use MetaDataAdapter i clear the cache each time i changed the model file. Then i noticed that the content of one file (meta-path_to_cache_users-users.php) change every time i change the MySQL schema of the users Table. When the schema is with foreign keys, the file meta-path_to_cache_users-users.php contains something like this:

<?php return array (
  0 => array (
    0 => 'id',
    1 => 'name',
    2 => 'email',
    3 => 'password',
    4 => 'mustChangePassword',
    5 => 'profilesId',
    6 => 'companyId',
    7 => 'notyficationId',
    8 => 'banned',
    9 => 'suspended',
    10 => 'active',
  ),
  1 => array (
    0 => 'id',
    1 => 'profilesId',
    2 => 'companyId',
  ),
  2 => array (
    0 => 'name',
    1 => 'email',
    2 => 'password',
    3 => 'mustChangePassword',
    4 => 'notyficationId',
    5 => 'banned',
    6 => 'suspended',
    7 => 'active',
  ),
...
?>

after i remove the foreign keys from schema the files contains this:

<?php return array (
  0 => array (
    0 => 'id',
    1 => 'name',
    2 => 'email',
    3 => 'password',
    4 => 'mustChangePassword',
    5 => 'profilesId',
    6 => 'companyId',
    7 => 'notyficationId',
    8 => 'banned',
    9 => 'suspended',
    10 => 'active',
  ),
  1 => array (
    0 => 'id',
  ),
  2 => array (
    0 => 'name',
    1 => 'email',
    2 => 'password',
    3 => 'mustChangePassword',
    4 => 'profilesId',
    5 => 'companyId',
    6 => 'notyficationId',
    7 => 'banned',
    8 => 'suspended',
    9 => 'active',
  ),
  ...
?>

and all sql queries are correct, we can see that only one part are move from one key to another. (i create a diagram to illustrate your my schema relationship, you can see it here)

EDIT:

i try this out but again nothing happend.

Try to change snapshots with:

   public function initialize() {
       $this->keepSnapshots(true);
   }

Thanks guys, I tried another app, and I have the same issue. But this time I don't use InnoDB, but MyISAM (so no relationships). And I have the same problem, the indexes goes in the WHERE clause when I update my row...

The models generated by Phalcon dev-tools are without relationships (belongs to, has many, etc.).

I hope this "bug" will be resolved soon.

Hello again, I just read more similar posts and I tried another thing.

In PHPMyAdmin, I opened the indexes of my table:

http://img15.hostingpics.net/pics/185434phalconkeys.png

And on the row PRIMARY contains all my indexes. So I remove everything execpted my "id" column. And after running my site and update() or save()...
it's worked !

So try to check your indexes too, maybe there are put in PRIMARY column and phalcon don't update PRIMARY column.

Someone can confirm ?