[SOLVED] Empty values being saved with Composite Primary Key?

30 Posts

neilness Reply #1, 15 years, 3 months ago

Below is my model schema. It allows users to "favorite" arbitrary objects, like blog posts, comments, etc. I feel like it makes most sense to store these as a composite primary key.

<object class="acUserFavorite" table="ac_user_favorite" extends="xPDOObject">
	<field key="object_class" dbtype="varchar" precision="20" phptype="string" null="false" index="pk" />
		<!-- object_class is a string identifying the type of object: acBlogPost, acComment, etc.. -->
	<field key="object_id" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" index="pk" />
	<field key="user_id" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" index="pk" />
	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
		<column key="object_class" length="" collation="A" null="false" />
		<column key="object_id" length="" collation="A" null="false" />
		<column key="user_id" length="" collation="A" null="false" />
	</index>
</object>

If I manually enter rows into the database I can retrieve and remove them, but trying to save rows with xPDO only creates null values, and then throws a duplicate key error.

Is there a problem with my schema? I can’t figure out why it won’t save the correct values, yet still retrieves them.

I was able to get this working with a simple auto-increment primary key (xPDOSimpleObject), but I feel that that method is unsustainable. After a few months the primary key would be in the millions.

Edit: I’ve tried removing the length attribute in the index, but It doesn’t seem to make a difference. I basically copied the format from the modX schema.

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 3 months ago

The schema seems fine. Can you provide the code you are using to save and retrieve the objects?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

30 Posts

neilness Reply #3, 15 years, 3 months ago

Sure. The code below works fine when the object extends xPDOSimpleObject with a simple primary key. remove() works in either case. save() doesn’t work with a composite primary key.

<?php
//$modx->log(modX::LOG_LEVEL_DEBUG,'Item: '.$_REQUEST['itemclass'].' - ID: '.$_REQUEST['itemid'].' - USER: '.$modx->user->id);

$fave = $modx->getObject('acFavorite', array(
	'user_id' => $modx->user->id,
	'object_class' => $_REQUEST['itemclass'],
	'object_id' => $_REQUEST['itemid'],
));

if (!$fave) {

	$fave = $modx->newObject('acFavorite', array(
		'user_id' => $modx->user->id,
		'object_class' => $_REQUEST['itemclass'],
		'object_id' => $_REQUEST['itemid'],
	));

	$fave->save();

} else {

	$fave->remove();

}

and the error message:

[2011-07-14 17:13:07] (ERROR @ /ext/favorite.php) Error 23000 executing statement:
INSERT INTO `modx_ac_favorite` (`object_class`, `object_id`, `user_id`) VALUES ('0', 0, 0)
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '0-0-0' for key 'PRIMARY'
)

I’m sure the variables are being passed correctly. It gives the same error even if i hardcode the values.

30 Posts

neilness Reply #4, 15 years, 3 months ago

So, I just rebuilt the schema and regenerated the table, this time with different default values for each field.

It appears that it will only save what is marked as the default attribute...

<field key="object_class" dbtype="varchar" precision="20" phptype="string" null="false" default="acDocument" index="pk" />
<field key="object_id" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" default="23" index="pk" />
<field key="user_id" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" default="14" index="pk" />

The save() code will work once, and then generates this error:

[2011-07-14 17:29:05] (ERROR @ /ext/favorite.php) Error 23000 executing statement:
INSERT INTO `modx_ac_favorite` (`object_class`, `object_id`, `user_id`) VALUES ('acDocument', 23, 14)
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry 'acDocument-23-14' for key 'PRIMARY'
)

Edit: When all of the defaults are removed from the fields, it throws this error:

[2011-07-14 17:35:21] (ERROR @ /ext/favorite.php) Error 23000 executing statement:
INSERT INTO `modx_ac_favorite` () VALUES ()
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '0-0' for key 'PRIMARY'
)

MODX Staff
10,725 Posts

opengeek Reply #5, 15 years, 3 months ago

You cannot set primary key values when using the second parameter of newObject(). Set the primary key values using fromArray() after creating the instance with newObject() and make sure you set the parameter setPrimaryKeys equal to true.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek