Setting a CCK default value for old nodes

by jenny on 26 August 2008 - 09:21pm in

I added a brand-new cck field to a content type which already had a bunch of nodes, but previously had no cck fields. This new field needed to have a default value set for all existing nodes. Of course, editing an old node and saving it without making any changes would set an appropriate default value... but I couldn't manually do that for all the nodes.

Usually at this point, I'd go run a simple query on the content_type table to set a default value for all the nodes of that type. However, since this was the very first cck field for this content type, there were only a handful of entries in the content_type table (corresponding to nodes of this type that had been edited since adding the cck field). I needed a way to set a default for all nodes of this type.

I thought about just inserting an nid value for all the appropriate nodes into the table, but I wasn't sure where the vid value was coming from or how to generate it. After banging my head against the wall for quite some time, I finally realized that the nid and vid value pairs I was missing in my content_type table were sitting there in the node_revisions table the whole time:

select r.nid, r.vid from node_revisions r join node n on n.nid = r.nid where n.type = 'image';

I decided to only grab the latest vids (version ids), since that's what seemed to be happening upon an edit-and-save cycle:

select r.nid, max(r.vid) from node_revisions r join node n on n.nid = r.nid where n.type = 'image' group by r.nid;

...so long story short, I didn't need to generate anything, just copy the latest nid, vid pair over to my content_type table:

insert ignore into content_type_image (nid, vid) select r.nid, max(r.vid) from node_revisions r join node n on n.nid = r.nid where n.type = 'image' group by r.nid;

...which worked like a charm. MySQL was already configured by cck to give the default value I had in mind if none was specified, so I didn't need to go any further.

Note that the ignore directive to the insert command means leave alone any values that were already in this table... just insert the keys that didn't exist yet... which is exactly what we want in this case.

$0.02

Just my $0.02, but I believe CCK should have a built in ability to automatically update all of the existing "default value" fields when a new field is created, OR, rebuild the field on demand... basically an "on the fly" generation.

I use a textarea field for adsense placement on a classified website I maintain and I can make changes for all new classifieds, but not the existing which is a bit of a pain... Using cckoverride (i think that's it) I block all authenticated from editing the field but allow them to view. That way I can have the injected field in the posts, format it the way I want it and not have to worry about the users deleting the ads from their posts.

Not being a SQL or programming guru, I'm a bit timid to inject the above SQL command in to update all of the existing fields but I'll probably get around to it when I can backup the database...

I setup a node reference but I either don't know how to utilize it or it won't do what I need it to do and it sure would be nice to be able to manually (or automatically) update the default values of specific fields through the interface itself :) (Anybody up for a module challenge?)

Even simpler

Thanks, I came across this and it was very helpful... However, if all you need to copy over is the current node revision, the database query in your example is more complex than necessary (and in fact not always guaranteed to be correct, since some modules might arrange things so that the most recent vid is not actually the current published one, although that's probably pretty rare). The information you need is all stored in the node table, so I believe you just needed to run this:

INSERT IGNORE INTO content_type_image (nid, vid) SELECT nid, vid FROM node WHERE type = 'image';

Also, in the case where the table schema is not set to automatically insert your desired default value (which was the case I found myself in), you can pretty easily do this too by modifying the above query to be similar to the following:

INSERT IGNORE INTO content_type_image (nid, vid, field_myfieldname_value) SELECT nid, vid, 'The default value that I want for my field' FROM node WHERE type = 'image';

Right you are!

Thanks for the simplification -- at the time I was under the mistaken impression that the vids were being somehow generated by cck when a value changed... I hadn't quite grasped how node revisions worked yet. When I found the vids in the node_revisions table, I was only too happy to copy them over and end my headache. ...but you're right, copying from the node table is much easier and probably more correct -- I only very recently discovered that the current node revision number is stored there! :)