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.phalconphp.com/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 !



44.7k

Try $user->update. Save works with create and update and tries to do the correct thing. If there is a snapshot issue then it could confuse it.



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


44.7k

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.



44.7k
edited Dec '15

Try to change snapshots with:

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


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.



44.7k

@Damian, try removing all of the option argument from the relationship. So remove the entire fourth argument. In particular I want to see if Phalcon is acting weird due to the alias.

Also this part is strange because the first and third argument are the same but then you are assigning it to a different namespace and alias. That could be the problem causing some alias related code to go haywire.

    $this->hasOne('id', __NAMESPACE__ . '\Avatars', 'usersId', array(
            'alias' => 'avatar',
            'reusable' => true
        ));
        $this->hasOne('id', __NAMESPACE__ . '\Customers', 'usersId', array(
            'alias' => 'customers',
            'reusable' => true
        ));

If you do figure out that something is wrong then I urge you to create a minimal one file test of this before it is lost to memory. You can just define all of your models in the one file and then trim out all of the unneeded tables, fields and relationships until it is as minimal as it can be. Then created a SQL dump of that. Those sorts of things get fixed. I saw your edit that you have it working by removing the DB contraints and I'm not familiar with Postgres stuff so perhaps it is a bug.



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:

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



44.7k

You should probably take this to a github issue. Before you do it though you should probably try to distill the bug down into its smallest trigger point. So take the code out into just a single file and don't worry about namespaces. If you can reproduce the issue from just a single CLI script with DI a few models (you won't need a task, controller, app, router, etc) and then a very small SQL schema written onto the github issue text then you are much more likely to get someone to run it and figure out what is wrong.



44.7k

Github issues is the correct place to take more involved stuff that could very well be a bug. I just use the forums for wanting tips and having some obvious things pointed out to me.

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 ?