Skip to main content
April 11, 2017

Prevent ambiguity in Drupal 8 migration source query

by Richard Papp

  

If you customize the database query in Drupal 8 migration source plugins, you may run into an integrity constraint violation error. This can be resolved by setting an alias for the table.

In order to migrate only files attached to nodes of certain content types, we extended the Drupal 8 d6_file migration source plugin from the file module and adapted the database query accordingly:

namespace Drupal\custom_migration\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type",
 *   source_module = "system"
 * )
 */
class File extends \Drupal\file\Plugin\migrate\source\d6\File {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['node_type'])) {
      $query->innerJoin('upload', 'u', 'f.fid = u.fid');
      $query->innerJoin('node', 'n', 'u.nid = n.nid');
      $query->condition('n.type', (array) $this->configuration['node_type'], 'IN');
    }

    return $query;
  }

But running that migration failed because the file id (fid) was now ambiguous:

Integrity constraint violation: 1052 Column fid in on
clause is ambiguous: SELECT f.*, map.sourceid1 AS
migrate_map_sourceid1, map.source_row_status AS
migrate_map_source_row_status
FROM 
{files} f
INNER JOIN {upload} u ON f.fid = u.fid
INNER JOIN {node} n ON u.nid = n.nid
LEFT OUTER JOIN drupal.migrate_map_d6_file map ON fid = map.sourceid1
WHERE (n.type IN (:db_condition_placeholder_0,
:db_condition_placeholder_1, :db_condition_placeholder_2,
:db_condition_placeholder_3)) AND ((map.sourceid1 IS NULL) OR
(map.source_row_status = :db_condition_placeholder_4))
ORDER BY timestamp ASC, f.fid ASC; Array
(
    [:db_condition_placeholder_0] => page
    [:db_condition_placeholder_1] => article
    [:db_condition_placeholder_2] => press_release
    [:db_condition_placeholder_3] => news
    [:db_condition_placeholder_4] => 1
)

Drupal 8 silently alters the query by adding a join to a mapping table in order to look up migration data for the files:

LEFT OUTER JOIN drupal.migrate_map_d6_file map ON fid = map.sourceid1

As the d6_file source plugin doesn't set a table alias for the source field identifier, fid in the above mentioned join is ambiguous. The column exists both in the files and upload database tables. To resolve this just add an alias in MigrateSourceInterface::getIds():

namespace Drupal\custom_migration\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type",
 *   source_module = "system"
 * )
 */
class File extends \Drupal\file\Plugin\migrate\source\d6\File {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['node_type'])) {
      $query->innerJoin('upload', 'u', 'f.fid = u.fid');
      $query->innerJoin('node', 'n', 'u.nid = n.nid');
      $query->condition('n.type', (array) $this->configuration['node_type'], 'IN');
    }

    return $query;
  }

  /**
   * {@inheritdoc}
   */
  public function getIds() {
    $ids = parent::getIds();

    $ids['fid']['alias'] = 'f';

    return $ids;
  }

}

 

Tools

Comments

Name

Richard Papp

Richard Papp

In reply to by Clemens Tolboom (not verified)

I added the source module from the parent class Drupal\file\Plugin\migrate\source\d6\File. The information is used by the migrate_upgrade UI for showing what modules are being upgraded from the legacy sites. Hence the source module should state the module whose data is being migrated from the legacy site. In Drupal 6 there was no file module. Files were handled by the system module.

Wed, 07.11.2018 - 19:15 Permalink

Add new comment

Klartext

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA