We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Null foreign key on MySQL ORM

Hello,

For example, I have this model relationship:

CREATE TABLE `users` (
`userId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userName` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
`status` tinyint(4) DEFAULT '1',
PRIMARY KEY (`userId`)
) ENGINE=InnoDB

and

CREATE TABLE `products` (
`prId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`prCode` varchar(15) DEFAULT NULL,
`prName` varchar(50) DEFAULT NULL,
`prCreated` datetime DEFAULT NULL,
`prCreatedBy` int(10) unsigned NOT NULL,
`prModified` datetime DEFAULT NULL,
`prModifiedBy` int(10) unsigned DEFAULT NULL,
`prStatus` tinyint(4) DEFAULT '1',
PRIMARY KEY (`prId`),
KEY `prCreatedBy` (`prCreatedBy`),
KEY `prModifiedBy` (`prModifiedBy`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`prCreatedBy`) REFERENCES `users` (`userId`),
CONSTRAINT `products_ibfk_2` FOREIGN KEY (`prModifiedBy`) REFERENCES `users` (`userId`)
) ENGINE=InnoDB

I used the phalcon developer tool to generate the models on my app with foreign key option so that the initialize method of Products model have:

$this->belongsTo("prCreatedBy", "TBI\Models\Users", "userId", array("foreignKey"=>true,"alias"=>'UsersCreated'));
$this->belongsTo("prModifiedBy", "TBI\Models\Users", "userId", array("foreignKey"=>true,"alias"=>'UsersModified'));

The problem is that when I try saving new product contain data of:

...
prCreatedBy = 1;
prModifiedBy = null;
//it's common sense to have the user id of creator and empty user id of modifier user on newly created row, right?

I keep getting this error:

Value of field "prModifiedBy" does not exist on referenced table

I tried using value of new RawValue("NULL"), but no luck.
It seems that the ORM require the fk value to be not NULL since by using value of 1 (or any existing users), it works.

However, --as you might know-- inserting directly into MySQL is working just fine. Is this a bug or is there something I missed?

*As a quick workaround, I just removed the foreignKey parameter on the model initialization and handle any MySQL exception

Thanks,
Iman



51.2k

Yep. This is a 2 months old medium bug. I really hope that they will fix it soon...