Prevent ambiguity in Drupal 8 migration source query

Submitted by Richard Papp on Tue, 04/11/2017 - 19:48

If you customize the database query in 8 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 8 d6_file source plugin from the file and adapted the database query accordingly:

namespace \custom_migration\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type"
 * )
 */
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 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 .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
)

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

LEFT OUTER JOIN .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 \migrate_knime\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type"
 * )
 */
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

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.