Drupal 7 Migrate: How do I migrate a mult-field key into a single taxonomy term

Question

Background:
I am doing a migration on Drupal 7 using Migrate 7.x-2.5. And I am migration a Sql table into a Drupal taxonomy. The key to my source SQL table is comprised of three fields: element_id, scale_id, and category.

The taxonomy term name must be element_id-scale_id-category so that I can properly reference the taxonomy. Here is some sample data:

3.A.1 RW 1 None
3.A.1 RW 2 Up to and including 1 month
3.A.1 RW 3 Over 1 month, up to and including 3 months

So the taxonomy name for the first line above will be "3.1.1-RW-1" and the description will be none.

Here is my code so far:

$query = Database::getConnection('default', 'external_db')  
         ->select('ete_categories', 'ete')  
         ->fields('ete', array('element_id', 'scale_id', 'category', 'category_description'))  
         ->orderBy('element_id', 'ASC');  

// Create the source data feed using the variable $query

$this->source = new MigrateSourceSQL($query);   

//       D E F I N E       T H E       D E S T I N A T I O N

$this->destination = new MigrateDestinationTerm('ete_categories');    

//       D E F I N E       T H E       K E Y S     

$this->map = new MigrateSQLMap(
  //  We have to tell migrate what the source key is
  $this->machineName,
  array(
    'element_id' => array(
      'type' => 'varchar',
      'length' => 20,
      'not null' => TRUE,
      'description' => 'Element Id',
    ),
    'scale_id' => array(
      'type' => 'varchar',
      'length' => 3,
      'not null' => TRUE,
      'description' => 'Scale Id',
    ),
    'category' => array(
      'type' => 'decimal',
      'length' => 3,
      'not null' => TRUE,
      'description' => 'category',
    ),
  ),

  MigrateDestinationTerm::getKeySchema()
);

//       M A P       T H E       F I E L D S

//  So let's map the fields

$this->addFieldMapping('name', CONCAT(`element_id`,'-',`scale_id`,'-',`category`));
$this->addFieldMapping('description', 'category_description');

My Question
I can concatenate the three fields into one using command line SQL. However, I do not know how to do this within the confine of the migration modules.(I realize that the code "CONCAT(element_id,'-',scale_id,'-',category))" is not valid migration code.)

So how do I concatenate the three fields together into a single field and set the destination field "name" equal to the concatenated fields?

Thanks

Answer 1

In your migration class, you can override the Migration::prepareRow() method to add a new name field to your rows:

  public function prepareRow($row) {
    $row->name = "{$row->element_id}-{$row->scale_id}-{$row->category}";
    return parent::prepareRow($row);
  }

And then simply map that field:

    $this->addFieldMapping('name', 'name');

But for simple concatenation, you can also use SelectQuery::addExpression() to add the CONCAT(...) to your SQL query:

    $name_alias = $query->addExpression(
      "CONCAT(element_id,'-',scale_id,'-',category)",
      'name'
    );

And then simply map the expression to the name field:

    $this->addFieldMapping('name', $name_alias);

Answer 2

I get two different results with the about two pieces of code. There are 41 rows to migrate.

When I use the first piece of code using the prepareRow, it worked great. I migrated 41 rows with the correct data in the name fields.

The second approach (addExpression) also worked, but it only migrated 4 rows, which is the number of unique Element Id there are. However, the names did contain the element_id-scale_id-category.

Also, I believe the following code has one to many ")"

$name_alias = $query->addExpression(
  "CONCAT(element_id,'-',scale_id,'-',category)",
  'name'
);