0

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

migrated from stackoverflow.com Nov 18 '13 at 7:01

This question came from our site for professional and enthusiast programmers.

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);
0

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'
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.