I have a use case where during the migration of a taxonomy vocabulary from a D6 site to a D7 site, I need to split out the terms into two separate, new, vocabularies. Doing the splitting is easy enough, and I can do it with a custom table, an extension of DrupalTerm6Migration, and an implementation of prepareRow().

function nb_migrate_register_migrations() {
  // Define arguments used for all migrations.
  $common_arguments = array(
    'source_connection' => 'legacy',
    'source_version' => 6,
  );

  $vocabulary_arguments = array(
    array(
      'class_name' => 'NBPeopleOrganizationsTermMigration',
      'description' => t('Migration of People and Organizations terms from Drupal 6'),
      'machine_name' => 'PeopleOrganizations',
      'source_vocabulary' => '4',  // D6 Vocabulary ID
      'destination_vocabulary' => 'non_journalists',
    ),
  );

  $common_vocabulary_arguments = $common_arguments + array(
    'soft_dependencies' => array('NBUser'),
  );
  foreach ($vocabulary_arguments as $arguments) {
    $arguments += $common_vocabulary_arguments;
    Migration::registerMigration($arguments['class_name'], $arguments['machine_name'],
      $arguments);
  }
}

class NBPeopleOrganizationsTermMigration extends DrupalTerm6Migration {
  public function __construct(array $arguments) {
    parent::__construct($arguments);
  }

  /**
   * Implementation of Migration::prepareRow().
   *
   * Need to redirect terms into either Journalists or Non-Journalists vocabularies
   * based on whether term is listed in nb_migrate_journalists table.
   *
   * @param $row
   */
  public function prepareRow($row) {
    if (parent::prepareRow($row) === FALSE) {
      return FALSE;
    }
    static $journalists = array();
    if (!$journalists) {
      $sql = "SELECT * FROM nb_migrate_journalists";
      $result = db_query($sql);
      foreach ($result as $record) {
        $journalists[] = $record->tid;
      }
    }

    // Check to see if term is in $journalists.  If not, leave it as
    // non_journalists (as defined in migration registration), otherwise
    // change it to journalists.
    if (in_array($row->tid, $journalists)) {
      $row->destination_vocabulary = 'journalists';
    }
  }
} 

When I try to run this particular migration (via drush migrate-import), I get the following error:

Migration failed with source plugin exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'tid' in where clause is ambiguous

which doesn't make sense to me, since I haven't modified the query to select the terms from D6 in the first place. If I use the default DrupalTerm6Migration class, it works fine. In fact, the error hits before prepareRow() is called (and I've verified that my class is registered by looking in the migrate_status table). I've tried to track in down in my debugger, but haven't had any luck yet.

Am I doing something wrong that's causing this error?

Thanks.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

wonder95’s picture

It turns out the problem has to do with something I didn't specify above in how I was starting the migration, and that's passing an idlist option to the drush migrate-import command. When I call it like so:

drush migrate-import PeopleOrganizations --idlist=706,2812,732

it chokes when building the query. I tracked it through SelectQuery::_toString() (in Drupal core select.inc), and this is the query that is generated:

SELECT DISTINCT td.tid AS tid, td.name AS name, td.description AS description, td.weight AS weight
FROM 
{term_data} td
LEFT OUTER JOIN {term_hierarchy} th ON td.tid=th.tid
WHERE  (vid IN  (:db_condition_placeholder_0)) AND (tid IN  (:db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3)) 
ORDER BY parent ASC

where

  • :db_condition_placeholder_0 = 4 (the vid for the source D6 vocabulary)
  • :db_condition_placeholder_1 = 706
  • :db_condition_placeholder_2 = 2812
  • :db_condition_placeholder_3 = 732

as contained in $this->$query->where['conditions']. Looking at the query at this point you can see the error, because both the term_data and term_hierarchy table have tid values, so since the tid values don't have a table prefix (e.g. td.tid or th.tid) the error is thrown. So as I see it, unless I'm missing something else, a table prefix needs to be added to the field name in the WHERE condition when the idlist option is passed to migrate-import for taxonomy term migrations.

wonder95’s picture

Title: Ambiguous tid in WHERE clause when extending DrupalTerm6Migration » Need table prefix in query when using idlist option with migrate-import drush command
Category: Support request » Bug report
wonder95’s picture

Project: Drupal-to-Drupal data migration » Migrate

Switching this to Migrate issues queue since drush commands are provided there, not in migrate_d2d.

roberttstephens’s picture

Actually, it looks like it should be fixed in migrate_d2d.

This seems to be caused by an alias not being used when the source key is set. When --idlist is used in drush, the migrate module will concatonate the alias to the field name in migrate/plugins/sources/sql.inc around line 258.

The source key is set around line 55 in migrate_d2d/taxonomy.inc

$this->map = new MigrateSQLMap($this->machineName,                                  
  array(                                                                            
    'tid' => array('type' => 'int',                                                 
                   'unsigned' => TRUE,                   
                   'not null' => TRUE,                   
                   'description' => 'Source term ID',    
                  ),                        
  ),                                        
  MigrateDestinationTerm::getKeySchema()    
);

It looks like $query objects in the d5, d6, and d7 taxonomy migrations specify an alias for td, so it's safe to add the alias to the sourceKey in taxonomy.inc.

roberttstephens’s picture

roberttstephens’s picture

Project: Migrate » Drupal-to-Drupal data migration
Assigned: Unassigned » roberttstephens
Status: Active » Needs review

I'm reasonably sure that the patch provided will fix the issue and that it should be fixed in migrate_d2d. My apologies if I'm mistaken. I'm switching this ticket back to migrate_d2d and setting the status to "Needs review".

wonder95, would you be able to confirm whether or not this fixes your issue?

mikhailian’s picture

@robertstephens,

If I may reply instead of wonder95… I had exactly the same problem, and your patch solves it.

Thanks alot!

mikhailian’s picture

Status: Needs review » Reviewed & tested by the community
mikeryan’s picture

Title: Need table prefix in query when using idlist option with migrate-import drush command » Need table alias in query when using idlist option with migrate-import drush command
Status: Reviewed & tested by the community » Fixed

Committed, thanks.

  • Commit 542bd79 on 7.x-2.x authored by roberttstephens, committed by mikeryan:
    Issue #2160027 by roberttstephens: Add table alias to taxonomy source...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.