Posted by anrikun on June 8, 2009 at 4:32pm
| Project: | Content Taxonomy |
| Version: | 6.x-1.x-dev |
| Component: | Miscellaneous |
| Category: | feature request |
| Priority: | major |
| Assigned: | tonychiu25 |
| Status: | needs review |
| Issue tags: | CCK, content taxonomy, taxonomy, terms |
Issue Summary
I used to use the traditional taxonomy field to add taxonomy to my content.
Now that I want to migrate to Content Taxonomy, I need a way to import my taxonomy relations so far.
Is there a way to do it?
Thanks!
Comments
#1
+1
#2
I think this is a duplicate of #368918: Keep core taxonomy & CCK taxonomy synced
#3
A simple SQL to copy a trad taxonomy's data to a single value content_taxonomy field:
UPDATE content_type_TYPENAME AS ctrINNER JOIN term_node AS tn ON tn.vid = ctr.vid
INNER JOIN term_data AS td ON td.tid = tn.tid AND td.vid = VOCABULARY_ID
INNER JOIN node AS n ON n.vid = tn.vid AND n.type = 'TYPENAME'
SET ctr.field_FIELDNAME_value = tn.tid
Replace TYPENAME by the name of the content type containing the single value content_taxonomy field.
Replace VOCABULARY_ID by the the ID of the vocabulary linked to the content_taxonomy field.
Replace field_FIELDNAME by the name of the content_taxonomy field.
Don't use this SQL on a multiple value content_taxonomy field.
#4
Attached is a script that will let you import from core taxonomy into CCK content_taxonomy fields, both single- and multiple-valued. You do need to configure it manually by editing the code, hopefully the comments are clear enough =)
The script will spit back an SQL file. You can analyze the file to see if everything's correct, and then proceed to import it using MySQL.
Maybe this could become part of the main module?
PS: I'm available for freelance projects =)
#5
Nice initiative janusman. I had to correct the table name on line 110, but for the rest it saved me a ton of word.
Corrected version attached.
#6
Thanks a lot @janusman. Works flawlessly and saved me a ton of work.
#7
Is there a way to migrate values from cck taxonomy to traditional taxonomy .... i used the default option store in cck table only .. now i need it in the taxonomy as well ... is there a way or do i have to write my own upgrade script???
#8
@shady_gun:
That really should be a separate issue. Having said that, migrating from cck taxonomy to core taxonomy is easy: just enable "Save values additionally to the core taxonomy system" and then use a bulk operation that opens and saves all nodes.
#9
Subscribing....
#10
Tracking; this would be quite helpful.
Edit: and the script from #4/#5 saved me a heap of time, thanks!
#11
Marked #689332: Pick up on existing taxonomy when changing to CT as duplicate of this issue.
#12
Marked #371651: Import Taxonomy data on new field instance as duplicate of this issue.
#13
Thanks, I was able to do what I needed based on this. However, I noticed that this script doesn't correctly separate different revisions (although it tries to?) the result being that the last revision gets all the terms from all revisions and the other revisions get none.
In addition, it is assuming that shared CCK fields are used, i.e. defining the field once and then using that field in other node types by selecting existing field, which results in one field for all terms for all revisions for all node types. This is in fact what I ended up doing because it's so easy and my site is not huge, but for performance reasons on a huge site the admin might choose to use separate fields instead and for this script to work in that scenario it would have to take the node type into consideration.
I was working with multivalue vocabularies only.
#14
While the script from #5 works, it doesn't integrate well if the migration needs to be automated. If I can make the assumption that the migration will first be tested on a copy of the live database in a development environment, it makes things a lot easier.
The following code can be put into a custom update function or a php file that gets executed with
drush script the_name_of_the_file.php.<?php
// Edit this mapping to match your vocabulary IDs and field names.
$mapping = array(
10 => 'field_tags',
11 => 'field_location',
);
$result = db_query('SELECT nid FROM {node}');
while ($nid = db_result($result)) {
$node = node_load($nid);
foreach ($mapping as $vid => $field_name) {
$node->{$field_name} = array();
}
foreach ($node->taxonomy as $tid => $term) {
if (array_key_exists($term->vid, $mapping)) {
$node->{$mapping[$term->vid]}[] = array(
'value' => $term->tid,
);
}
}
node_save($node);
}
Notes:
* This code snippet erases any existing content for the targeted content_taxonomy fields.
* This code snippet currently doesn't do anything to handle node revisions, but a few additional lines of code should do it.
#15
subscribe, greetings, Martijn
#16
Hi,
It would be great if the script also worked with prefix database tables.
I am not able to get it working out of the box. Anyone has solved this please?
EDIT: I allways get the following error:
Could not query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TN.nid' at line 1greetings,
Martijn
#17
For the project that I'm working on this would be a great feature too. I'll give the script a try to see if it works on our servers. Could it be possible to just integrate this so that the values are actually retrieved from the taxonomy for that node and taxonomy vocabulary if no value is set for cck?
Perhaps this isn't a great suggestion but just a thought.
#18
subscribing
#19
sub
#20
I'm having the same issue as #16.
UPDATE: Issue was caused by me leaving empty keys and values in the array where you specify vid and field_name
#21
The script proves that "migrating" a traditional taxonomy field into a CCK field is possible,
What we'd need now is an integration mechanism to gain flexibility, similar to the "Retroactive updates"/ "Active updating" done by the FileField Paths module.
#22
THX, the fix in #5 worked for me. I changed the script to handle database prefixes as well.
@Summit: You need to remove the empty rows from the array ( '' => '', ).
#23
@jakewalk (and @janusman) thank you. You just saved me a ton of time.
@jakewalk you forgot to add the table prefix into the get_vid_from_nid() function. I added a global for $db_prefix in the function and added it to the query there as well.
#24
@chales: would you mind sharing your fixes?
#25
Here ya go.
This version has full table prefix support throughout. I've successfully updated several content types that used core taxonomy to using Content Taxonomy with Hierarchical Select options.
In case anyone wonders, this does work with the dev version. I'm using the 6.x-1.x-dev (2010-07-11) version of Content Taxonomy.
#26
I think I could work this into a patch; just want to hear from the module mantainer if there is an interest in reviewing it. I'd use the Batch API like the FileField module (from @asb's suggestion in #21).
#27
@chales,
this version is missing the section on
/**
* Create SQL to import single-valued CCK fields
*/
Can you please update it and re-post?
Thanks!
#28
Oops, I uploaded one I had stripped down. Let's try this again.
#29
Hey Everyone!
Great script and WHOA did it save me loads of time. I have one problem with it, is nobody else seeing this happen? The outputted file appears to have values for all content types on my website findinjp.com.
For example, I have content type restaurant, sightseeing, entertainment, hotels, and dailylife.
I have my config file set as below:
// Content type you're trying to map to
// Example: $content_type = 'story';
$content_type = 'restaurants';
but in the resulting list, I get nid and vid's for sightseeing, entertainment, and restaurants. I think this doesn't result in a problem because SQL will just ignore the values because it will only update content_type_restaurants. So this is fine, but it's a lot of wasted code and more lines of sql code to run, for very large sites with lots of nodes.
I can't really understand the code below the config section to make the necessary changes, but does anyone else see this problem or know how to change the code so that the unneeded lines of sql are not generated?
Here is a excerpt from the resulting sql file. nid 186 is a sightseeing content type and most of the others are restaurants. Why is 186 being put out here?
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=132 AND vid=132;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=133 AND vid=133;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=150 AND vid=150;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=176 AND vid=176;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=183 AND vid=183;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=185 AND vid=185;
UPDATE content_type_restaurants SET field_prefecture_value=176 WHERE nid=186 AND vid=186;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=188 AND vid=188;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=189 AND vid=189;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=190 AND vid=190;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=191 AND vid=191;
UPDATE content_type_restaurants SET field_prefecture_value=74 WHERE nid=192 AND vid=192;
#30
I might be doing something wrong, but I can get this script to spit out single value terms, but now I'm trying to get it to work with multiple value terms. And here is what the fix.sql file says.
Could not query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TN.nid' at line 1
Here are my settings:
// FIRST: CCK content_taxonomy fields that are configured to have SINGLE VALUE
$vid_to_field_singlevalue = array(
// Example: you have a Vocabulary "Color" with vid = 1, and want to import that into fieldname field_color
// '1' => 'field_color',
// Fill these ...
// '3' => 'field_restauranttype',
// '' => '',
// '' => '',
);
// SECOND: CCK content_taxonomy fields that are configured to have MULTIPLE VALUES
$vid_to_field_multivalue = array(
// Example: you have a Vocabulary "Country" with vid = 2, and want to import that into fieldname field_country
// '2' => 'field_country',
// Fill these ...
'3' => 'field_randomkeywords',
// '' => '',
// '' => '',
);
#31
Absolutely brilliant, saved me a lot of time
#32
@drupalfan81
remove the empty fields from the settings, it should work fine.
#33
Just a quick question...
Does this patch take into account the term hierarchy?
#34
Has anyone tried to put this code into a configurable action for VBO and Rules?
#35
Thanks, worked great!
#36
Hi Guys,
I'm back trying to upgrade my site and add the other vocabularies into CCK Content Taxonomy fields. I'm completely baffled that this isn't a part of the module yet. Does the above script still work? Why hasn't this feature been added to the module, I must be missing something as this module has about 30,000 sites using it. And I can't imagine EVERYONE started from scratch using this module. Some people would have to have been using Taxonomy terms and then later wanted to move them to CCK fields right???
#37
Yes, we all want this desparately, but it's by no means "critical" as defined in Priority levels of issues.
#38
Here's a patch to fix the problem with migrating traditional taxonomy to content taxonomy field. To perform the data migration, check the "Save values additionally to the core taxonomy system (into the 'term_node' table)" check box in the content taxonomy field configure page and hit save. This should perform the data migration from the appropriate rows in term_node table to the content_field_... table.
#39
#40
Hi tonychiu25, Thanks! This will save lots of time. It worked for me, although I noticed when I tried it that it only worked if I had "Unlimited" selected for the number of terms in the CCK Content Taxonomy field. Might be worth adding a warning message or something to that effect.