Tuesday, September 20, 2011

Drupal Migrate Multiple Taxonomy Terms

I stumbled upon a blog post yesterday discussing a way to import a node with multiple terms tied it:

http://stellapower.net/blog/migrate-module-migrating-nodes-taxonomy-terms

I wanted to offer up an alternative approach that I've utilized that hopefully is easier to use without using hook_migrate_prepare_node.  I will start by assuming that our source data has a table I'll call node, with a 1 to many relationship with a term table.  From there, let's build a simple query object with just a couple of fields (not all that you would need, just an example).


$subquery = db_select('term', 't2');
$subquery->join('node', 'n2', 't2.nid=n2.nid');
$subquery->groupBy('t2.nid');
$subquery->addField('t2', 'nid');
$subquery->addExpression("GROUP_CONCAT(t2.label SEPARATOR '|')", 'labels');

$query = db_select('node', 'n')
	 ->fields('n', array('nid'));
$query->join($subquery, 't', 'n.nid=t.nid');
$query->addField('t', 'labels');

This example uses the built in pdo objects to build the select and then we can use mysql's GROUP_CONCAT function to build a column containing multiple values.  The migrate module the has built in support for separators via a function on a field mapping:

$this->addFieldMapping('field_terms', 'labels')
     ->separator('|');

I've been doing migrations from MS Sql Server, so if you are in that world you might be saying "we don't have group_concat".  True, but we can slap a view with a little hack to do the concat for us and do a select from Drupal through that view.  In my case we'll use the '!' character as a space replacement to preserve the original whitespace.  With the xml path hack, it will become space separated which we will convert to '|' and then convert the '!' back to whitespace.

SELECT * FROM node n
LEFT OUTER JOIN (
	select t.nid, 
		REPLACE(REPLACE(RTRIM((
			SELECT CAST(REPLACE(dat.DrupalTerm, ' ', '!') AS VARCHAR(MAX)) + ' ' 
			FROM term t2 WITH (nolock)
			WHERE t2.tid = t.tid FOR XML PATH (''))), ' ', ',')
			, '!', ' ') AS labels
	from term t WITH (nolock)
	GROUP BY t.nid
) my_terms ON n.nid = my_terms.nid

As always, let me know if you have any questions!

No comments:

Post a Comment